asked on

Transact SQL help

I have a table in SQL Server 2008 that looks like the following

Period Amt1 Amt2 Amt3
Jan 100 50 25
Feb 60 100 30

What I need is:

Value JAN FEB
Amt1 100 60
Amt2 50 100
Amt3 25 30

I have tried the PIVOT statement but can only get that to work with one AMT value. Is there an easy way to do this?

ASKER CERTIFIED SOLUTION

Lowfatspread

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

d1cjm1ex

ASKER

a few syntax tweaks needed to make it work

d1cjm1ex

ASKER

To take this example one step further. If I had another column in my table, Region, can the PIVOT statement handle this?

I would want output like this:

Region Value Jan Feb
A Amt1 100 60
A Amt2 50 100
A Amt3 25 30
B Amt1 10 20
B Amt2 4 5
B Amt3 2 1

Lowfatspread

select region + '-' + case x.n when 1 then 'AMT1' when 2 then 'AMT2' else 'AMT3' end
as [region Type]
,period

d1cjm1ex

ASKER

Thanks. This did work, however, is there any way to keep the region as a separate column rather than a concatenation? Or is this a limit of the pivot command?

Thanks again for the help.

d1cjm1ex

ASKER

I figured out a solution using CHARINDEX. Thanks again for your help!