Crosstab?

eka_eka · November 9, 2018, 1:12pm

Muj9 · November 9, 2018, 1:12pm

sorry my mistake try now

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

eka_eka · November 9, 2018, 1:15pm

It did successfully run, but generated only zeros and pulled all categories on the left hand side, I only need that refer to parent_id = 136

Muj9 · November 9, 2018, 1:20pm

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
			where a.Parent_ID = 136
) main 
	pivot(count(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

eka_eka · November 9, 2018, 1:22pm

This pull exactly what I wanted, but it doesn't count by 'HS - Low (No Injury, Minor Injury)','HS - Medium (first Aid injury)' and 'HS - High (LTI, Disability, Fatality)' and returns zeros.

Muj9 · November 9, 2018, 2:01pm

double check your data as this is counting T which is the second table in your query and looks right to me

eka_eka · November 9, 2018, 2:14pm

I have just run my table with this and it clearly returns required data.

select [Incident].ID, [System_Category].Parent_ID,[System_Category].Title

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

ORDER BY [incident].ID

Muj9 · November 9, 2018, 2:16pm

send me a screen shot for this query

    select [Incident].ID, [System_Category].Parent_ID,[System_Category].Title
    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].Parent_ID = 136
    ORDER BY [incident].ID

eka_eka · November 9, 2018, 2:18pm

Muj9 · November 9, 2018, 2:50pm

run this please

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

 	select 
	[System_Category].Title, 
	[System_Category].Parent_ID
	into #temp 
	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



select a.*,b.Title as T
from (
	  select * from #temp
	  where 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 * from #temp
			where 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
where a.Parent_ID = 136

eka_eka · November 9, 2018, 2:57pm

Nothing is happening

eka_eka · November 9, 2018, 3:04pm

What is this about dropping table?

Muj9 · November 9, 2018, 3:14pm

select *  
from (
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 
where Parent_ID = 136 

)fox
	pivot(count(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

Muj9 · November 9, 2018, 3:19pm

did the above work?

eka_eka · November 9, 2018, 3:21pm

zeros only

Muj9 · November 9, 2018, 3:26pm

this is what @ScottPletcher suggested

try this:-

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

select 
[System_Category].Title, 
[System_Category].Parent_ID
into #temp
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


SELECT
	Title,
	SUM(CASE WHEN ranking = 'H&S - Low (No Injury, Minor Injury)'THEN 1 ELSE 0 END) AS [H&S - Low (No Injury, Minor Injury)],
	SUM(CASE WHEN ranking = 'H&S - Medium (first Aid injury)'THEN 1 ELSE 0 END) AS [H&S - Medium (first Aid injury)],
	SUM(CASE WHEN ranking = 'H&S - High (LTI, Disability, Fatality)' THEN 1 ELSE 0 END) AS [H&S - High (LTI, Disability, Fatality)]
FROM (		
		SELECT
		MAX(CASE WHEN is_ranking = 1 THEN '' ELSE Title END) AS Title,
		MAX(CASE WHEN is_ranking = 1 THEN Title ELSE '' END) AS ranking
		FROM #temp
		CROSS APPLY (SELECT CASE WHEN Title IN ('H&S - Low (No Injury, Minor Injury)','H&S - Medium (first Aid injury)','H&S - High (LTI, Disability, Fatality)') THEN 1 ELSE 0 END AS is_ranking) AS alias1
		GROUP BY Parent_ID 
	) AS derived
GROUP BY Title
ORDER BY Title

eka_eka · November 9, 2018, 3:28pm

That one is worse.

Muj9 · November 9, 2018, 3:29pm

its not easy trying to figure this out when I cant run the query and see results for myself I tried it on a dummy data and it seems to work fine

Muj9 · November 9, 2018, 3:30pm

my query and @ScottPletcher query produces the same results don't know why your data is not being pulled back

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

	Select * 
	into #temp
	from (
			select 1 Parent_ID,'H&S - Low (No Injury, Minor Injury)'Title
			union all select 1,'a'
			union all select 2,'b'
			union all select 2,'H&S - High (LTI, Disability, Fatality)'
			union all select 3,'c'
			union all select 3,'H&S - Low (No Injury, Minor Injury)'
			union all select 4,'b'
			union all select 4,'H&S - High (LTI, Disability, Fatality)'
			union all select 5,'c'
			union all select 5,'H&S - Medium (first Aid injury)'
			union all select 6,'H&S - Medium (first Aid injury)'
			union all select 6,'c'
			union all select 7,'a'
			union all select 7,'H&S - High (LTI, Disability, Fatality)'
			union all select 8,'a'
			union all select 8,'H&S - Medium (first Aid injury)'
	) main 




select * 
	from (
			select 
			 a.*
			,b.Title as T  
			from (select Title, Parent_ID from #temp where 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 Title, Parent_ID from #temp where 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(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





	SELECT
		Title,
		SUM(CASE WHEN ranking = 'H&S - Low (No Injury, Minor Injury)'THEN 1 ELSE 0 END) AS [H&S - Low (No Injury, Minor Injury)],
		SUM(CASE WHEN ranking = 'H&S - Medium (first Aid injury)'THEN 1 ELSE 0 END) AS [H&S - Medium (first Aid injury)],
		SUM(CASE WHEN ranking = 'H&S - High (LTI, Disability, Fatality)' THEN 1 ELSE 0 END) AS [H&S - High (LTI, Disability, Fatality)]
	FROM (		
			SELECT
			MAX(CASE WHEN is_ranking = 1 THEN '' ELSE Title END) AS Title,
			MAX(CASE WHEN is_ranking = 1 THEN Title ELSE '' END) AS ranking
			FROM #temp
			CROSS APPLY (SELECT CASE WHEN Title IN ('H&S - Low (No Injury, Minor Injury)','H&S - Medium (first Aid injury)','H&S - High (LTI, Disability, Fatality)') THEN 1 ELSE 0 END AS is_ranking) AS alias1
			GROUP BY Parent_ID 
		) AS derived
	GROUP BY Title
	ORDER BY Title

eka_eka · November 9, 2018, 3:30pm

Wonder how much time I spent to make it working until decided to seek for help here.