CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlpivot
ChrisD
ChrisD

Reputation: 319

TSQL Pivot 4 value columns

In SQL Server 2008 I'm trying to 'pivot' the table format below to wide format using the Period column (in the real data there are 5 different Periods).

I've searched but not yet found a solution to this. I've referred to https://www.tangrainc.com/blog/2009/01/pivoting-on-multiple-columns/#comment-504 but cannot translate the logic to >2 value columns - which I need.

Any thoughts? You may have guessed I'm no SQL expert. Using SQL Server 2008.

Thanks, Chris

ps. first S/O post!

Trying to get from a flat table:

enter image description here

to a wide table:

enter image description here

Upvotes: 3

Views: 76

Answers (1)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can use conditional aggregation:

select Cat, Dept,
       sum(case when Period = 'LW' then New else 0 end) as [Net LW],
       sum(case when Period = 'LY' then New else 0 end) as [Net LY],
       sum(case when Period = 'LW' then Gross else 0 end) as [Gross LW],
       sum(case when Period = 'LY' then Gross else 0 end) as [Gross LY],
       sum(case when Period = 'LW' then Profit else 0 end) as [Profit LW],
       sum(case when Period = 'LY' then Profit else 0 end) as [Profit LY],
       sum(case when Period = 'LW' then Units else 0 end) as [Units LW],
       sum(case when Period = 'LY' then Units else 0 end) as [Units LY]
from table t
group by Cat, Dept;

Upvotes: 5

Related Questions