SQL Query help - SQL Server Forums

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]

IK1972

56 Posts

Posted - 2014-10-29 : 18:28:18

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources