Wednesday, 4 September 2013

Merging pivot results

Merging pivot results

I'm attempting to pivot a few values into columns but I can't seem to make
them all end up in the same row. I'm not sure if there's a way to just
specifically write a pivot query or if there's something wrong with how
I'm attempting to join them. Because you probably know better than myself,
Stackoverflow, I'll leave my terrible attempts out and try to explain
it...
select
[CARRIAGE_ID]
,[PUBLISH_FLAG]
,[COUNTER_PRESCRIBED]
,[ADT] AS 'ADT2008'
,[AWT] AS 'AWT2008'
,[AWE] AS 'AWE2008'
from [RISSxplr].[dbo].[ADT_MAP_FACT]
pivot (max([FIGURE]) FOR COUNT_TYPE IN ([ADT],[AWT],[AWE])) AS pCountType
WHERE [COUNT_YEAR] = 2008
Gives me a result like (sample):
CARRIAGE_ID PUBLISH_FLAG COUNTER_PRESCRIBED ADT2008 AWT2008 AWE2008
10041 1 P NULL NULL 36800
10041 1 P NULL 46400 NULL
10041 1 P 43000 NULL NULL
These all share the same CARRIAGE_ID and what I need is for them to all be
in the same row so it'd look like this:
CARRIAGE_ID PUBLISH_FLAG COUNTER_PRESCRIBED ADT2008 AWT2008 AWE2008
10041 1 P 43000 46400 36800
But I can't seem to make that happen. Help please! And thank you, as always.

No comments:

Post a Comment