December 12, 2011 at 12:25 pm
How can I PIVOT this... All the columns and their count in two columns side by side.
select
sum(case when t.Application_ID = 4 then 1 else 0 end) as 'AP Images',
sum(case when t.Application_ID = 8 then 1 else 0 end) as 'AP Exchange',
sum(case when t.Application_ID = 128 then 1 else 0 end) as 'AP Archive',
sum(case when t.Application_ID in (4,8) then 1 else 0 end) as 'AP Images and AP Exchange',
sum(case when t.Application_ID in (4,128) then 1 else 0 end) as 'AP Images and AP Archive',
sum(case when t.Application_ID in (8,128) then 1 else 0 end) as 'AP Archive and AP Exchange',
sum(case when t.Application_ID in (8,128,4) then 1 else 0 end) as 'AP Images AP Archive AP Exchange'
from (
select a.Application_ID,
mo.MDMOrganizationName,
COUNT(*) count
from tbl_MDMOrganizations mo (Nolock)
inner join
(select o.mdmid, o.Organization_ID, o.OrganizationType_ID from tbl_Organizations o (Nolock)
inner join type_Organization t (Nolock) on o.OrganizationType_ID = t.OrganizationType_ID
---- Filter out anonymous and Internal
where t.OrganizationType_ID not in (0, 1) and o.MDMID is not null ) o
on mo.MDMID = o.MDMID and mo.Status = 1
inner join map_Organization_Application moa (Nolock)
on o.Organization_ID = moa.Organization_ID
inner join tbl_Applications a (Nolock)
on moa.Application_ID = a.Application_ID
and a.Application_ID in (4, 8, 128)
group by a.Application_ID, mo.MDMOrganizationName
) t
December 12, 2011 at 1:06 pm
Please don't cross post. Direct all replies to the original thread. http://qa.sqlservercentral.com/Forums/Topic1220407-391-1.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply