Help with Query for Visual Studios Report

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.

All Forums

Development Tools

Reporting Services Development

Help with Query for Visual Studios Report

Author

Topic

mikecoleman407
Starting Member

13 Posts

Posted - 2015-04-07 : 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 - 2015-04-08 : 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]