SQLTeam.com | Weblogs | Forums

Crosstab?


#1

Would be easier to show than explain what I'm trying to achieve. Please help!

Capture


#2
	if OBJECT_ID('tempdb..#temp') is not null drop table #temp

	Select * 
	into #temp
	from (
			select 1 ID,'low' value
			union all select 1,'a'
			union all select 2,'b'
			union all select 2,'high'
			union all select 3,'c'
			union all select 3,'low'
			union all select 4,'b'
			union all select 4,'high'
			union all select 5,'c'
			union all select 5,'mid'
			union all select 6,'mid'
			union all select 6,'c'
			union all select 7,'a'
			union all select 7,'high'
			union all select 8,'a'
			union all select 8,'mid'
	) main 



	select * 
	from (
			select a.*,b.value as T  
			from (select value, ID from #temp where value in ('a','b','c'))a
			left join (select value, ID from #temp where value in ('low','mid','high')) b on a.ID = b.ID
	) main 
	pivot(count(ID) for T in ([Low],[mid],[high]))pt

#3
SELECT
    value,
    SUM(CASE WHEN ranking = 'low' THEN 1 ELSE 0 END) AS low,
    SUM(CASE WHEN ranking = 'mid' THEN 1 ELSE 0 END) AS mid,
    SUM(CASE WHEN ranking = 'high' THEN 1 ELSE 0 END) AS high
FROM (
    SELECT
        MAX(CASE WHEN is_ranking = 1 THEN '' ELSE value END) AS value,
        MAX(CASE WHEN is_ranking = 1 THEN value ELSE '' END) AS ranking
    FROM #temp
    CROSS APPLY (
        SELECT CASE WHEN value IN ('low', 'mid', 'high') THEN 1 ELSE 0 END AS is_ranking
    ) AS alias1
    GROUP BY ID 
) AS derived
GROUP BY value
ORDER BY value

#4

Scott, may I ask for some comments in your code?

Where does my "from/join" goes? Also, why sum is being used when it should count identical values instead.

I have tried playing with it, but because I don't fully understand it, I cannot make it working.

Below is as far as I went, please help.

SELECT
[System_Category].Title,
SUM(CASE WHEN ranking = 'H&S - Low' THEN 1 ELSE 0 END) AS low,
SUM(CASE WHEN ranking = 'H&S - Medium' THEN 1 ELSE 0 END) AS mid,
SUM(CASE WHEN ranking = 'H&S - High' THEN 1 ELSE 0 END) AS high
FROM (
SELECT
MAX(CASE WHEN is_ranking = 1 THEN '' ELSE [System_Category].Title END) AS title,
MAX(CASE WHEN is_ranking = 1 THEN [System_Category].Title ELSE '' END) AS ranking
FROM
----------------
[Envoy].[dbo].[Incident]
JOIN [System_Category_Module] on [System_Category_Module].Module_ID = [Incident].ID
JOIN [System_Category] on [System_Category].ID = [System_Category_Module].Category_ID
----------------
CROSS APPLY (
SELECT CASE WHEN [System_Category].Title IN ('H&S - Low', 'H&S - Medium', 'H&S - High') THEN 1 ELSE 0 END AS is_ranking
) AS alias1
GROUP BY [Incident].ID
) AS derived
GROUP BY [System_Category].Title
ORDER BY [System_Category].Title


#5

Are you not using my code because the first table is specifying values to be used?

then just use this:-

	select * 
	from (
			select a.*,b.value as T  
			from (select value, ID from #temp where value not in ('low','mid','high'))a
			left join (select value, ID from #temp where value in ('low','mid','high')) b on a.ID = b.ID
	) main 
	pivot(count(ID) for T in ([Low],[mid],[high]))pt

#6

Muj9, to be honest, I don't understand your solution even less than Scott's, but great thank you for effort.


#7

May be I just explained it vaguely. Let me pull some 'live' data, maybe this will make more sense.


#8

Just select all the Titles you want to appear as columns in a list like so :-
('H&S - Medium (first Aid injury)','more','more','more') and use this in the query below in both lines where it says in and not in and also use the column heading in the very last line but make sure you warp the Title values in "[" for example in the where clause they would appear in string 'H&S - Medium (first Aid injury)' but in the pivot in it would be like so [H&S - Medium (first Aid injury)]

 select *
 from (
 select a.*,b.Title as T
 from (select Title, Parent_ID from Youtable where Title not in ('H&S - Medium (first Aid injury)','more'))a
 left join (select Title, Parent_ID from Yourtable where Title in ('H&S - Medium (first Aid injury)','more')) b on a.Parent_ID = b.Parent_ID
 ) main
 pivot(count(Parent_ID) for T in ([H&S - Medium (first Aid injury)],[more]))pt

#9

I think you missing 'Moving Plant/Vehicle', 'Housekeeping', etc. - all those under Parent_ID = 136

This is what came up.


#10

in you last line do this

For T in ([H&S - Medium (first Aid injury)],[other values],[more]))pt


#11

copy and paste the code you showed as screen shot and I will amend it


#12

select *
from (
select a.*,b.[System_Category].Title as T

			from (select [System_Category].Title, [System_Category].Parent_ID 
					
					from [Envoy].[dbo].[Incident] JOIN [System_Category_Module] on [System_Category_Module].Module_ID = [Incident].ID 
												  JOIN [System_Category] on [System_Category].ID = [System_Category_Module].Category_ID 
					
					where [System_Category].Title not in ('H&S - Low (No Injury, Minor Injury)','H&S - Medium (first Aid injury)','H&S - High (LTI, Disability, Fatality)'))a
			
			left join (select [System_Category].Title, [System_Category].Parent_ID 
					   
					   from [Envoy].[dbo].[Incident] JOIN [System_Category_Module] on [System_Category_Module].Module_ID = [Incident].ID 
													 JOIN [System_Category] on [System_Category].ID = [System_Category_Module].Category_ID 
					   
					   where [System_Category].Title in ('H&S - Low (No Injury, Minor Injury)','H&S - Medium (first Aid injury)','H&S - High (LTI, Disability, Fatality)')) b on a.[System_Category].Parent_ID = b.[System_Category].Parent_ID
	) main 
	
	pivot(count([System_Category].Parent_ID) for T in ('H&S - Low (No Injury, Minor Injury)','H&S - Medium (first Aid injury)','H&S - High (LTI, Disability, Fatality)'))pt

#13
select *  
from (
 					select 
					a.*,
					b.[System_Category].Title as T
					from (
					select [System_Category].Title, [System_Category].Parent_ID
					from [Envoy].[dbo].[Incident]
					JOIN [System_Category_Module] on [System_Category_Module].Module_ID = [Incident].ID
					JOIN [System_Category] on [System_Category].ID = [System_Category_Module].Category_ID
					where [System_Category].Title not in ('H&S - Low (No Injury, Minor Injury)','H&S - Medium (first Aid injury)','H&S - High (LTI, Disability, Fatality)'))a
left join (
					select 
					[System_Category].Title, 
					[System_Category].Parent_ID 
					from [Envoy].[dbo].[Incident] 
					JOIN [System_Category_Module] on [System_Category_Module].Module_ID = [Incident].ID 
					JOIN [System_Category] on [System_Category].ID = [System_Category_Module].Category_ID 
					where [System_Category].Title in ('H&S - Low (No Injury, Minor Injury)','H&S - Medium (first Aid injury)','H&S - High (LTI, Disability, Fatality)')) b on a.[System_Category].Parent_ID = b.[System_Category].Parent_ID
) main 
	
	pivot(count([System_Category].Parent_ID) for T in ([H&S - Low (No Injury, Minor Injury)],[H&S - Medium (first Aid injury)],[H&S - High (LTI, Disability, Fatality)]))pt

#14

did the above work?


#15


#16
select *  
from (		select 
			a.*,
			b.Title as T
			from (
			select [System_Category].Title, [System_Category].Parent_ID
			from [Envoy].[dbo].[Incident]
			JOIN [System_Category_Module] on [System_Category_Module].Module_ID = [Incident].ID
			JOIN [System_Category] on [System_Category].ID = [System_Category_Module].Category_ID
			where [System_Category].Title not in ('H&S - Low (No Injury, Minor Injury)','H&S - Medium (first Aid injury)','H&S - High (LTI, Disability, Fatality)'))a
left join (
			select 
			[System_Category].Title, 
			[System_Category].Parent_ID 
			from [Envoy].[dbo].[Incident] 
			JOIN [System_Category_Module] on [System_Category_Module].Module_ID = [Incident].ID 
			JOIN [System_Category] on [System_Category].ID = [System_Category_Module].Category_ID 
			where [System_Category].Title in ('H&S - Low (No Injury, Minor Injury)','H&S - Medium (first Aid injury)','H&S - High (LTI, Disability, Fatality)')
			) b on a.[System_Category].Parent_ID = b.[System_Category].Parent_ID
) main 
	
	pivot(count([System_Category].Parent_ID) for T in ([H&S - Low (No Injury, Minor Injury)],[H&S - Medium (first Aid injury)],[H&S - High (LTI, Disability, Fatality)]))pt

#17

now?


#18


#19
select *  
from (		select 
			a.*,
			b.Title as T
			from (
			select [System_Category].Title, [System_Category].Parent_ID
			from [Envoy].[dbo].[Incident]
			JOIN [System_Category_Module] on [System_Category_Module].Module_ID = [Incident].ID
			JOIN [System_Category] on [System_Category].ID = [System_Category_Module].Category_ID
			where [System_Category].Title not in ('H&S - Low (No Injury, Minor Injury)','H&S - Medium (first Aid injury)','H&S - High (LTI, Disability, Fatality)'))a
left join (
			select 
			[System_Category].Title, 
			[System_Category].Parent_ID 
			from [Envoy].[dbo].[Incident] 
			JOIN [System_Category_Module] on [System_Category_Module].Module_ID = [Incident].ID 
			JOIN [System_Category] on [System_Category].ID = [System_Category_Module].Category_ID 
			where [System_Category].Title in ('H&S - Low (No Injury, Minor Injury)','H&S - Medium (first Aid injury)','H&S - High (LTI, Disability, Fatality)')
			) b on a.Parent_ID = b.Parent_ID
) main 
	
	pivot(count([System_Category].Parent_ID) for T in ([H&S - Low (No Injury, Minor Injury)],[H&S - Medium (first Aid injury)],[H&S - High (LTI, Disability, Fatality)]))pt

#20

it should work now