April 8, 2009 at 8:46 am
I have the following query:
select type, istatus, count(logical_name)
from dbo.devicem1
group by type, istatus
order by type, istatus
The possible values of istatus are:
ARCHIVED, AVAILABLE, BUILD, OPERATIONAL, ORDERED, RECEIVED, REQUESTED, Retired, TEST or the value can be NULL.
I am turning this into a Pivot table query and have the following working at this point:
select type as 'TYPE',
[ARCHIVED] as 'ARCHIVED',
[AVAILABLE] as 'AVAILABLE',
[BUILD] as 'BUILD',
[OPERATIONAL] as 'OPERATIONAL',
[ORDERED] as 'ORDERED',
[RECEIVED] as 'RECEIVED',
[REQUESTED] as 'REQUESTED',
[Retired] as 'Retired',
[TEST] as 'TEST',
[ARCHIVED]+[AVAILABLE]+[BUILD]+[OPERATIONAL]+[ORDERED]+[RECEIVED]+[REQUESTED]+[Retired]+[TEST] as TOTALS
from
(select type, istatus, logical_name
from dbo.devicem1
group by type, istatus, logical_name) p
pivot (count(logical_name)
for istatus in ([ARCHIVED],[AVAILABLE],[BUILD],[OPERATIONAL],[ORDERED],[RECEIVED],[REQUESTED],[Retired],[TEST])
) as DevicePivot
order by type
With the first basic query, I get a count back for the number of logical_names there are where istatus is NULL within a type. With the Pivot table query, I have not been able to replicate retrieving the count for the NULL values. I am fairly new to this level of SQL coding. Can anyone suggest how I can retrieve the NULL count in the second query?
Thanks.
April 8, 2009 at 9:23 am
Have you tried using IsNull on the column? You might not be able to do that directly in the Pivot operator, but if you first use a CTE to query the table, and use IsNull in the CTE, then build the Pivot on the results of the CTE, you should be able to get what you need.
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 8, 2009 at 11:52 am
I have tried variations with IsNull to no avail. I haven't tried with a CTE first as I am not that familiar with it but I will check it out.
Thanks for the reply.
April 8, 2009 at 12:16 pm
I got it to work with the following code:
select type as 'TYPE',
[ARCHIVED] as 'ARCHIVED',
[AVAILABLE] as 'AVAILABLE',
[BUILD] as 'BUILD',
[OPERATIONAL] as 'OPERATIONAL',
[ORDERED] as 'ORDERED',
[RECEIVED] as 'RECEIVED',
[REQUESTED] as 'REQUESTED',
[Retired] as 'Retired',
[TEST] as 'TEST',
[BLANK] as 'NULL',
[ARCHIVED]+[AVAILABLE]+[BUILD]+[OPERATIONAL]+[ORDERED]+[RECEIVED]+[REQUESTED]+[Retired]+[TEST]+[BLANK] as TOTALS
from
(select type, isnull(istatus,'BLANK') as istatus, logical_name
from dbo.devicem1
group by type, istatus, logical_name) p
pivot (count(logical_name)
for istatus in ([ARCHIVED],[AVAILABLE],[BUILD],[OPERATIONAL],[ORDERED],[RECEIVED],[REQUESTED],[Retired],[TEST],[BLANK])
) as DevicePivot
order by type
April 8, 2009 at 12:54 pm
Cool biz. Always good to defeat one of these ones that looks daunting at first, isn't it?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 24, 2013 at 11:34 pm
Thank you very much, this also sloves my problem 🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply