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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
d1cjm1exAuthor Commented:
a few syntax tweaks needed to make it work
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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

LowfatspreadCommented:
select region + '-' + case x.n when 1 then 'AMT1' when 2 then 'AMT2' else 'AMT3' end
                       as [region Type]
                    ,period
               
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.
d1cjm1exAuthor Commented:
I figured out a solution using CHARINDEX.  Thanks again for your help!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.