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?



d1cjm1exAsked:
Who is Participating?
 
LowfatspreadCommented:
select [type] as [value], jan,feb
  from (select case x.n when 1 then 'AMT1' when 2 then 'AMT2' else 'AMT3' end
                       as [Type]
                    ,period
                   ,case x.n when 1 then AMT1 when 2 then AMT2 else AMT3 end
                       as [Value]
              from yourtable as a
               , (select 1 as n union select 2 union select 3) as x  
            ) as y
  pivot (sum ([value] for period in  (JAN,FEB))as p
0
 
d1cjm1exAuthor Commented:
a few syntax tweaks needed to make it work
0
 
d1cjm1exAuthor Commented:
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
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
LowfatspreadCommented:
select region + '-' + case x.n when 1 then 'AMT1' when 2 then 'AMT2' else 'AMT3' end
                       as [region Type]
                    ,period
               
0
 
d1cjm1exAuthor Commented:
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.
0
 
d1cjm1exAuthor Commented:
I figured out a solution using CHARINDEX.  Thanks again for your help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.