October 27, 2014 at 7:55 pm
-- drop table #temp
create table #temp(ID int identity, LID int, EventName varchar(50), StatusCode varchar(5))
insert into #temp(LID, EventName, StatusCode) values
(1, 'Event1', 'QAC'),
(1, 'Event2', 'QAF'),
(1, 'Event3', 'QAR'),
(1, 'Event4', 'QAU'),
(1, 'Event5', null),
(1, 'Event6', null),
(1, 'Event7', 'QAF'),
(2, 'Event3', 'QAR'),
(2, 'Event4', 'QAU'),
(2, 'Event5', null)
select * from #temp
-- Expected Result:
LID, QAC_Count, QAF_Count, QAR_Count, QAU_Cout, Null_Count
1 , 1 , 2 , 1 , 1 , 2
2 , 0 , 0 , 1 , 1 , 1
I wrote this query but I just want to check if there is more effecift way to write this.
select distinct t.LID, qac.QAC_Count, qaf.QAF_Count, qar.QAR_Count, qau.QAU_Count, qan.Null_Count
from #temp t
cross apply
(
select count(*) QAC_Count from #temp it
where it.LID = t.LID and it.StatusCode = 'QAC'
) qac
cross apply
(
select count(*) QAF_Count from #temp it
where it.LID = t.LID and it.StatusCode = 'QAF'
) qaf
cross apply
(
select count(*) QAR_Count from #temp it
where it.LID = t.LID and it.StatusCode = 'QAR'
) qar
cross apply
(
select count(*) QAU_Count from #temp it
where it.LID = t.LID and it.StatusCode = 'QAU'
) qau
cross apply
(
select count(*) Null_Count from #temp it
where it.LID = t.LID and it.StatusCode is null
) qan
October 27, 2014 at 8:23 pm
Hi
The following should provide the result you want and perform better
SELECT LID,
SUM(CASE WHEN StatusCode = 'QAC' THEN 1 ELSE 0 END) QAC_COUNT,
SUM(CASE WHEN StatusCode = 'QAF' THEN 1 ELSE 0 END) QAF_COUNT,
SUM(CASE WHEN StatusCode = 'QAR' THEN 1 ELSE 0 END) QAR_COUNT,
SUM(CASE WHEN StatusCode = 'QAU' THEN 1 ELSE 0 END) QAU_COUNT,
SUM(CASE WHEN StatusCode is null THEN 1 ELSE 0 END) NULL_COUNT
FROM #temp
GROUP BY LID
November 5, 2014 at 5:09 am
That solution is fine as long as all the possible status codes are known. If they are not then you would need to use the PIVOT() function along with some dynamic SQL to determine the pivoted column names. There are loads of examples both on SQL server central and on the web in general
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply