staircase behavior with pivoted data

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

staircase behavior with pivoted data

Author

Topic

jsmith789
Starting Member

1 Post

Posted - 2010-10-04 : 15:43:32

Hail to the fellow programmers and query writers,

I have this beautiful query


SELECT ID, [1] AS coL1, [15] AS coL2, [2] AS coL3, [16] AS coL4, [12] AS coL5
FROM MY_TABLE
PIVOT (sum(INT_VALUE) FOR FUND_CODE IN ([1],[2],[15],[16],[12])) AS p
--GROUP BY ID, [1] , [15]  , [2] , [16] , [12] 
ORDER BY ID ASC

That returns me data like this:


10001      182       NULL      NULL      NULL
10001      NULL      81        NULL      NULL
10001      NULL      NULL      182       NULL
10001      NULL      NULL      NULL      81
10002      165       NULL      NULL      NULL
10002      NULL      73        NULL      NULL
10002      NULL      NULL      165       NULL
10002      NULL      NULL      NULL      73

The 10001 and 10002 are two primary keys, and I'd like to show my data like this:


10001      182       81      182      81
10002      165       73      165      73

I tried this commented GROUP BY to no avail. Any hints? Does it involve COALESCE?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2010-10-04 : 16:01:35

Your code (as is) worked for me. If you're still having trouble, post some executable sample code as I have to show your problem.


declare @t table (id int, int_value int, fund_code int)
insert @t 
select 10001, 1, 1 union all
select 10001, 2, 2 union all
select 10001, 4, 2 union all
select 10001, 8, 2 union all
select 10001, 4, 15 union all
select 10001, 8, 16 union all
select 10001, 8, 12 union all
select 10002, 1, 1 union all
select 10002, 2, 2 union all
select 10002, 4, 15 union all
select 10002, 8, 16 union all
select 10002, 8, 12 


SELECT ID, [1] AS coL1, [15] AS coL2, [2] AS coL3, [16] AS coL4, [12] AS coL5
FROM @t
PIVOT (sum(INT_VALUE) FOR FUND_CODE IN ([1],[2],[15],[16],[12])) AS p
ORDER BY ID ASC

OUTPUT:
ID          coL1        coL2        coL3        coL4        coL5
----------- ----------- ----------- ----------- ----------- -----------
10001       1           4           14          8           8
10002       1           4           2           8           8

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources