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 |
|
johniom
Starting Member
1 Post |
 Posted - 2010-03-19 : 05:54:09
|
| Hiya All,Hiya All,I am trying making a report for work, which I want to count a row at a time (as the figures will change for every client).The Column names are as follows: - ClientID, Safety, Residential Vocational, Independence, Timing and Community Family, Alternative Options.All the columns apart from clientid have v1aa, v1ab, and v1ac in them.What I’m trying to do is count up all the v1aa, v1ab and v1ac for each client and if its v1aa it called Red if its v1ab its called blue or its v1ac its called yellow – which should only total up to 6 per row.Therefore, it looks something likeClient ID: 1010 Red: 3 Blue: 2 yellow: 1 Client ID: 2010 Red: 2 Blue: 3 yellow: 1Client ID: 2010 Red: 3 Blue: 3 yellow: 0Could anyone point me how to do this? Thanks |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-03-19 : 12:19:17
|
| [code]SELECT ClientID,SUM(CASE WHEN Val = 'v1aa' THEN 1 ELSE 0 END) AS RED,SUM(CASE WHEN Val = 'v1ab' THEN 1 ELSE 0 END) AS BLUE,SUM(CASE WHEN Val = 'v1ac' THEN 1 ELSE 0 END) AS YellowFROM Table tUNPIVOT ( Val FOR Category IN ([Safety],[Residential Vocational],[Independence],[Timing and Community Family],[Alternative Options]))uGROUP BY ClientID[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
|
|
|
|
|
SQL Count