SQLTeam.com | Weblogs | Forums

Help creating pivot table, so far stuck


#1

Hi everyone,

I'm a newbie, sorry if this seems stupid, but can anyone help me, Ive the following query:

select PBDate, Datename(WeekDay, PBDate) as MyDay, Station, sum(CASE WHEN NG IS NULL THEN bcount ELSE 0 END) as Good, sum(CASE WHEN NG is not null THEN 1 ELSE 0 END) as [No Good]
from ProcessDetails where PBDate between '07/16/2018' and '07/20/2018'
group by PBDate, STATION

That gives me this result:

PBDate MyDay Station Good No Good
07/16/2018 Monday THONE 142 0
07/17/2018 Tuesday THEND 534 0
07/16/2018 Monday SMALL 90 8
07/18/2018 Wednesday THEND 26 0
07/17/2018 Tuesday THWORK 75 0
07/17/2018 Tuesday THONE 414 0

I want to achive this:

Since I'll be color coding the headers of Good->Yellow and No_Good->Light Blue, I don't actually need those headers.

I've try using pivot but so far no success, can some one please show me how to achieve this, it is very frustrating.

Thanks in advanced for your help


#2
Select 
	Station,
	Sum([G_Monday]) As [Good Monday],
	Sum([NG_Monday]) As [No Good Monday],
	Sum([G_Tuesday]) As [Good Tuesday],
	Sum([NG_Tuesday]) As [No Good Tuesday],
	Sum([G_Wednesday]) As [Good Wednesday],
	Sum([NG_Wednesday]) As [No Good Wednesday],
	Sum([G_Thursday]) As [Good Thursday],
	Sum([NG_Thursday]) As [No Good Thursday],
	Sum([G_Friday]) As [Good Friday],
	Sum([NG_Friday]) As [No Good Friday]
From
(
	Select
		'G_' + Datename(WeekDay, PBDate) as MyDayGood,
		'NG_' + Datename(WeekDay, PBDate) as MyDayNoGood, 
		Station,
		CASE WHEN NG IS NULL THEN bcount ELSE 0 END as Good,
		CASE WHEN NG is not null THEN 1 ELSE 0 END as [NoGood]
	from @ProcessDetails 
	where PBDate between '07/16/2018' and '07/20/2018'
) PD
Pivot
(
	Sum(Good) For MyDayGood In ([G_Monday],[G_Tuesday],[G_Wednesday],[G_Thursday],[G_Friday])
) PVT_GOOD
Pivot
(
	Sum([NoGood]) For MyDayNoGood In ([NG_Monday],[NG_Tuesday],[NG_Wednesday],[NG_Thursday],[NG_Friday])
) PVT_NOGOOD
Group By Station

#3

Wow! Thank you very much AndyC, you rock! The only thing I'm still trying to understand is why it is not returning 8 on station SMALL for the NG Monday, please check out the results:

Station Good Monday No Good Monday Good Tuesday No Good Tuesday Good Wednesday No Good Wednesday Good Thursday No Good Thursday Good Friday No Good Friday
SMALL 90 1 NULL NULL NULL NULL NULL NULL NULL NULL
THEND NULL NULL 534 0 26 0 NULL NULL NULL NULL
THONE 142 0 414 0 NULL NULL NULL NULL NULL NULL
THWORK NULL NULL 75 0 NULL NULL NULL NULL NULL NULL

If I run the the query:

Select
		'G_' + Datename(WeekDay, PBDate) as MyDayGood,
		'NG_' + Datename(WeekDay, PBDate) as MyDayNoGood, 
		Station,
		CASE WHEN NG IS NULL THEN bcount ELSE 0 END as Good,
		CASE WHEN NG is not null THEN 1 ELSE 0 END as [NoGood]
	from @ProcessDetails 
	where PBDate between '07/16/2018' and '07/20/2018'

The result is correct, 8 records with 1 on No Good, but it seems is not doing the sum!

Can you please share what it is happening, thank you so much for your kind help.


#4

Try replacing the first derived table with:

Select
	'G_' + Datename(WeekDay, PBDate) as MyDayGood,
	'NG_' + Datename(WeekDay, PBDate) as MyDayNoGood, 
	Station,
	sum(CASE WHEN NG IS NULL THEN bcount ELSE 0 END) as Good,
	sum(CASE WHEN NG is not null THEN 1 ELSE 0 END) as [NoGood]
from @ProcessDetails 
where PBDate between '07/16/2018' and '07/20/2018' 
group by PBDate,station

If not, you might need to post some sample data (preferably as some SQL inserts) so we can see what data you're working with


#5

AndyC ,

Thank you so much! That did it!, my website is now complete thanks to your help.

Best regards