Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
oahu9872
Posting Yak Master
112 Posts |
 Posted - 2009-06-24 : 22:31:44
|
| I have a table called Encounters. It has two fields: MemberID and EncounterType. The MemberID is unique to each member and the EncounterType has 3 possible values: Phone, Mail, Web.Members may have multiple encounters, so I could have the following rows:MemberID EncounterType1 Call1 Call1 Web1 Mail2 Call2 WebI want to produce a summary that would display each unique memberid and a count of each of encounter in a single field. Something like this:2 Call, 1 Mail, 1 Web.Is this possible? Maybe something like this:select memberid, 'Call'& count(case where encountertype = 'Call')from encountersgroup by memberidAny help would be appreciated. Thank You. |
|
|
Directory
Starting Member
5 Posts |
Posted - 2009-06-24 : 22:48:57
|
| If you are using MSSQL 2005 you can use the PIVOT function.This topic will help to you.http://msdn.microsoft.com/en-us/library/ms177410.aspx |
 |
|
|
waterduck
Aged Yak Warrior
982 Posts |
Posted - 2009-06-24 : 22:59:58
|
| SELECT [Call], [Web], [Mail]FROM (SELECT MemberID, EncounterType FROM encounters)aPIVOT (COUNT(MemberID) FOR EncounterType IN ([Call], [Web], [Mail])) as b |
|
|
|
|
|
|
Multiple SubSelect Statements