Query Help
-
Hello Everyone
I need one help to develop my logic
create table #x2
(
CourseID varchar(10),
ScheduleTerms Varchar(2)
)
insert into #x2 values ('000541','Q1')
insert into #x2 values ('000541','Q2')
insert into #x2 values ('000541','Q3')
insert into #x2 values ('000541','Q4')
insert into #x2 values ('001951','Q1')
insert into #x2 values ('001951','Q2')
insert into #x2 values ('001951','Q3')
insert into #x2 values ('001951','Q4')
It gives me output as below
CourseIDScheduleTerms
000541Q1
000541Q2
000541Q3
000541Q4
001951Q1
001951Q2
001951Q3
001951Q4
The desired output is
CourseIDQ1Q2Q3Q4
0005411111
0019511111
Please help me to display in the bit format as desired output.
Thanks
-
This is called a cross Tab.
The simplest method is
SELECT
CourseId
,SUM(CASE WHEN ScheduleTerms = 'Q1' THEN 1 ELSE NULL END) [Q1]
,SUM(CASE WHEN ScheduleTerms = 'Q2' THEN 1 ELSE NULL END) [Q2]
,SUM(CASE WHEN ScheduleTerms = 'Q3' THEN 1 ELSE NULL END) [Q3]
,SUM(CASE WHEN ScheduleTerms = 'Q4' THEN 1 ELSE NULL END) [Q4]
FROM
#x2
GROUP BY
CourseId
_________________________________________________________________________
SSC Guide to Posting and Best Practices -
SELECT CourseID,
MAX(CASE WHEN ScheduleTerms='Q1' THEN 1 ELSE 0 END) AS Q1,
MAX(CASE WHEN ScheduleTerms='Q2' THEN 1 ELSE 0 END) AS Q2,
MAX(CASE WHEN ScheduleTerms='Q3' THEN 1 ELSE 0 END) AS Q3,
MAX(CASE WHEN ScheduleTerms='Q4' THEN 1 ELSE 0 END) AS Q4
FROM #x2
GROUP BY CourseID
ORDER BY CourseID;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
or use PIVOT
select CourseID, [Q1], [Q2], [Q3], [Q4]
from #x2 p
pivot (count(ScheduleTerms) FOR ScheduleTerms IN ([Q1],[Q2],[Q3],[Q4])) pvt
BTW, Cross-tab based on WHEN is usually faster...
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply