SQL 2005 Problem with pivot - could be because is varchar of date? 1

[Welshbird]

Morning all.

I have a query that I really want to pivot in output. I've looked at everything and I really can't see where this is going wrong...
My query looks like the following:

select CCGHB, Brand from
(
-- actually query by yer)
) pvt
pivot 
	(sum(TotalNIC) for 
	MQT in ([Feb 13],[May 13],[Aug 13],[Nov 13])) as Value

I've run the actually query unpivoted and get the correct results but it just won't pivot.
Result example from the middle query:

CCGHB	         Brand	         MQT	TotalNIC
NHS BEXLEY CCG	Decapeptyl	Feb 13	207
NHS BEXLEY CCG	Prostap DCS	Feb 13	23324
NHS BEXLEY CCG	Zoladex	          Feb 13	73220
NHS BEXLEY CCG	Decapeptyl	May 13	207
NHS BEXLEY CCG	Prostap DCS	May 13	29419
NHS BEXLEY CCG	Zoladex	          May 13	78635
NHS BEXLEY CCG	Prostap DCS	Aug 13	25205
etc...

Any thoughts? Am tempted to bash head against wall at this point......

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[sabinUE]

hi,

;with yourQuery

AS
	(select 'NHS BEXLEY CCG' as CCGHB,'Decapeptyl' as Brand,'Feb 13' as MQT,207 as TotalNIC union all
	 select 'NHS BEXLEY CCG','Prostap DCS','Feb 13',23324 union all
	 select 'NHS BEXLEY CCG','Zoladex','Feb 13',73220 union all
	 select 'NHS BEXLEY CCG','Decapeptyl','May 13',207 union all
	 select 'NHS BEXLEY CCG','Prostap DCS','May 13',29419 union all
	 select 'NHS BEXLEY CCG','Zoladex','May 13',78635 union all
	 select 'NHS BEXLEY CCG','Prostap DCS','Aug 13',	25205 )

select * from
(
	select * from yourQuery
) pvt
pivot 
	(sum(TotalNIC) for MQT in ([Feb 13],[May 13],[Aug 13],[Nov 13])) as Value

the output

CCGHB	Brand	Feb 13	May 13	Aug 13	Nov 13
NHS BEXLEY CCG	Decapeptyl	207	207	NULL	NULL
NHS BEXLEY CCG	Prostap DCS	23324	29419	25205	NULL
NHS BEXLEY CCG	Zoladex	73220	78635	NULL	NULL

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabin

 

[Welshbird]

BUt I get no pivot.

AM using 2005 - but this should stil lwork? It works in other cases!

ARGGGGG

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[Welshbird]

If I run your code exactly as is - then I get the correct answer.

But when I run my query i don't. Curiouser and curiouser....

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[Welshbird]

This is the line to produce the date - am I being really dense?

right(convert(varchar(20),period,06), len(convert(varchar(20),period,06)) -3) as MQT,

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[Welshbird]

YEY!

Used the ;with FeeQuery as
then it works!

I don't really understand why at the moment, but perhaps I'll worry about that after I have worked out how to finish the rest of it.

THanks Muchly,


Fee

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[sabinUE]

with welcome

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabin

 

[drlex]

Fee, I'm sure George will offer the full explanation; ISTR that datetimes are held as two integers; days after 1st Jan 1900 and then 300ths/second after midnight, so you're right to convert a datetime to a varchar for pivoting.

Since convert format 6 always gives 2 characters for the day part, you could simplify your grouping expression to SUBSTRING(convert(varchar(20),period,6),4,6) to give the 3 character month and 2 digit year.

Happy Friday,
lex

soi là, soi carré

 

[audiopro]

I was going to ask what a pivot was but then realised that this is proper SQL not the Lego MySQL that I am used to.

Keith

http://www.studiosoft.co.uk