Help creating pivot table, so far stuck

Transact-SQL
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:

V2
V2.jpg743×400 33.3 KB

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
1 Like
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

1 Like
6

hi

i know this post is from long ago

It also has a solution

I am practicing my SQL
Also I have given a different solution

If it helps GREAT
:slight_smile:
:slight_smile:

I have added a "rn" column in the create data
to order by the station

drop create data 
use tempdb
go 


drop table data
go 

create table data
(
rn int, 
PBDate date , 
MyDay varchar(100) , 
Station varchar(100) , 
Good int , 
NoGood int  
)
go 


insert into data select  1,'07/16/2018','Monday','THONE',142,0
insert into data select  2,'07/17/2018','Tuesday','THEND',534,0
insert into data select  3,'07/16/2018','Monday','SMALL',90,8
insert into data select  2,'07/18/2018','Wednesday','THEND',26,0
insert into data select  4,'07/17/2018','Tuesday','THWORK',75,0
insert into data select  1,'07/17/2018','Tuesday','THONE',414,0
go 

select * from data
go
SQL 
SELECT rn, 
       station, 
       Sum(( CASE 
               WHEN myday = 'Monday' THEN good 
               ELSE 0 
             END )) AS monday_good, 
       Sum(( CASE 
               WHEN myday = 'Monday' THEN nogood 
               ELSE 0 
             END )) monday_nogood, 
       Sum(( CASE 
               WHEN myday = 'Tuesday' THEN good 
               ELSE 0 
             END )) tuesday_good, 
       Sum(( CASE 
               WHEN myday = 'Tuesday' THEN nogood 
               ELSE 0 
             END )) tuesday_nogood, 
       Sum(( CASE 
               WHEN myday = 'Wednesday' THEN good 
               ELSE 0 
             END )) wednesaday_good, 
       Sum(( CASE 
               WHEN myday = 'Wednesday' THEN nogood 
               ELSE 0 
             END )) wednesday_nogood, 
       Sum(( CASE 
               WHEN myday = 'Thursday' THEN good 
               ELSE 0 
             END )) thursday_good, 
       Sum(( CASE 
               WHEN myday = 'Thursday' THEN nogood 
               ELSE 0 
             END )) thursday_nogood, 
       Sum(( CASE 
               WHEN myday = 'Friday' THEN good 
               ELSE 0 
             END )) friday_good, 
       Sum(( CASE 
               WHEN myday = 'Friday' THEN nogood 
               ELSE 0 
             END )) friday_nogood 
FROM   data 
GROUP  BY rn, 
          station 
ORDER  BY rn 

go
Result

image
image.png1565×219 43 KB