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
 SQL Server 2012 Forums
 Transact-SQL (2012)
 SQL Query help

Author  Topic 

IK1972

56 Posts

Posted - 2014-10-27 : 21:50:23

-- 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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-10-27 : 23:03:22
[code]select *
from (
select LID, StatusCode = isnull(StatusCode, 'NULL')
from #temp
) d
pivot
(
count(StatusCode)
for StatusCode in ([QAC], [QAF], [QAR], [QAU], [NULL])
) p
[/code]


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

IK1972

56 Posts

Posted - 2014-10-29 : 18:28:18
Thanks
Go to Top of Page
   

- Advertisement -