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 
mikecoleman407
Starting Member
13 Posts 
Posted  20150407 : 21:42:33

I have some select statements that I would like to use for a report in VS2010. Is there a way to make this into one select statement so that I can create column output for each of the sites?
so for example, 'WSISBC' is a site. I would like to include all these sites in one select statement. WSISBC will have a count of 30 and WSISBH will have a count of 27 and so on.
Right now, if I put this in the query builder and run it in the report, it only gives me output for the first select statement. I am new to SQL so I appreciate your patience and excellence in knowledge.
select COUNT(EecEmplStatus) as 'WSISBC' from empcomp where eecemplstatus <> 'T'AND EecOrgLvl2 in ( 'WSISBC') select COUNT(EecEmplStatus) as 'WSISBH' from empcomp where eecemplstatus <> 'T'AND EecOrgLvl2 in ('WSISBH') select COUNT(EecEmplStatus) as 'WSISBM' from empcomp where eecemplstatus <> 'T'AND EecOrgLvl2 in ('WSISBM') select COUNT(EecEmplStatus) as 'WSISEL' from empcomp where eecemplstatus <> 'T'AND EecOrgLvl2 in ('WSISEL') select COUNT(EecEmplStatus) as 'WSISWH' from empcomp where eecemplstatus <> 'T'AND EecOrgLvl2 in ('WSISWH') select COUNT(EecEmplStatus) as 'WSIWSB' from empcomp where eecemplstatus <> 'T'AND EecOrgLvl2 in ('WSIWSB') select COUNT(EecEmplStatus) as 'WSIGAO' from empcomp where eecemplstatus <> 'T'AND EecOrgLvl2 in ('WSIGAO') select COUNT(EecEmplStatus) as 'WSIDOJ' from empcomp where eecemplstatus <> 'T'AND EecOrgLvl2 in ('WSIDOJ') select COUNT(EecEmplStatus) as 'WSIHMM' from empcomp where eecemplstatus <> 'T'AND EecOrgLvl2 in ('WSIHMM') select COUNT(EecEmplStatus) as 'WSIALC' from empcomp where eecemplstatus <> 'T'AND EecOrgLvl2 in ('WSIALC') select COUNT(EecEmplStatus) as 'WSIBRF' from empcomp where eecemplstatus <> 'T'AND EecOrgLvl2 in ('WSIBRF') select COUNT(EecEmplStatus) as 'WSIURE' from empcomp where eecemplstatus <> 'T'AND EecOrgLvl2 in ('WSIURE') select COUNT(EecEmplStatus) as 'WSIFNR' from empcomp where eecemplstatus <> 'T'AND EecOrgLvl2 in ('WSIFNR') select COUNT(EecEmplStatus) as 'WSIZSW' from empcomp where eecemplstatus <> 'T'AND EecOrgLvl2 in ('WSIZSW') select COUNT(EecEmplStatus) as 'WSIFMI' from empcomp where eecemplstatus <> 'T'AND EecOrgLvl2 in ('WSIFMI') select COUNT(EecEmplStatus) as 'WSIFWI' from empcomp where eecemplstatus <> 'T'AND EecOrgLvl2 in ('WSIFWI') select COUNT(EecEmplStatus) as 'WSIFMN' from empcomp where eecemplstatus <> 'T'AND EecOrgLvl2 in ('WSIFMN')


Ifor
Aged Yak Warrior
700 Posts 
Posted  20150408 : 09:14:40

[code] SELECT SUM(CASE WHEN EecOrgLvl2 = 'WSISBC' THEN 1 ELSE 0 END) AS WSISBC ,SUM(CASE WHEN EecOrgLvl2 = 'WSISBH' THEN 1 ELSE 0 END) AS WSISBH ,SUM(CASE WHEN EecOrgLvl2 = 'WSISBM' THEN 1 ELSE 0 END) AS WSISBM ,SUM(CASE WHEN EecOrgLvl2 = 'WSISEL' THEN 1 ELSE 0 END) AS WSISEL ,SUM(CASE WHEN EecOrgLvl2 = 'WSISWH' THEN 1 ELSE 0 END) AS WSISWH ,SUM(CASE WHEN EecOrgLvl2 = 'WSIWSB' THEN 1 ELSE 0 END) AS WSIWSB ,SUM(CASE WHEN EecOrgLvl2 = 'WSIGAO' THEN 1 ELSE 0 END) AS WSIGAO ,SUM(CASE WHEN EecOrgLvl2 = 'WSIDOJ' THEN 1 ELSE 0 END) AS WSIDOJ ,SUM(CASE WHEN EecOrgLvl2 = 'WSIHMM' THEN 1 ELSE 0 END) AS WSIHMM ,SUM(CASE WHEN EecOrgLvl2 = 'WSIALC' THEN 1 ELSE 0 END) AS WSIALC ,SUM(CASE WHEN EecOrgLvl2 = 'WSIBRF' THEN 1 ELSE 0 END) AS WSIBRF ,SUM(CASE WHEN EecOrgLvl2 = 'WSIURE' THEN 1 ELSE 0 END) AS WSIURE ,SUM(CASE WHEN EecOrgLvl2 = 'WSIFNR' THEN 1 ELSE 0 END) AS WSIFNR ,SUM(CASE WHEN EecOrgLvl2 = 'WSIZSW' THEN 1 ELSE 0 END) AS WSIZSW ,SUM(CASE WHEN EecOrgLvl2 = 'WSIFMI' THEN 1 ELSE 0 END) AS WSIFMI ,SUM(CASE WHEN EecOrgLvl2 = 'WSIFWI' THEN 1 ELSE 0 END) AS WSIFWI ,SUM(CASE WHEN EecOrgLvl2 = 'WSIFMN' THEN 1 ELSE 0 END) AS WSIFMN FROM empcomp WHERE eecemplstatus <> 'T'; [/code] 


mikecoleman407
Starting Member
13 Posts 
Posted  20150408 : 09:49:26

Thanks so much works like a champ. Can you tell me what the THEN 1 ELSE 0 does to the statement? 


Ifor
Aged Yak Warrior
700 Posts 
Posted  20150408 : 10:03:39

It produces either a 1 or a 0 which is then SUMmed. 


mikecoleman407
Starting Member
13 Posts 
Posted  20150409 : 13:33:22

Thanks again for all your help ! 




