SQL transpose data

Question

I need to transpose data that looks like the following. I don't need to use any aggregate functions, just want to transpose columns to rows.

Current view:

Name | Code1 | Code2 | Code3 | Pct1 | Pct2 | Pct3 | Amt1 | Amt2 | Amt3
Name1   123     124     125     50     25     25   1000    1500  1555
Name2    123    124     125     50     25     25   1222    1520  1600

What I Need:

AccountName   |    Code#   |       Pct   |         Amt
Name1              123             50             1000
Name1              124             25             1500
Name1              125             25             1555
Name2              123             50             1222
Name2              124             25             1520
Name2              125             25             1600

if this is possible, could you also include where I would place my joins if I need to use data in a different table?

I'm using SQL Server Management Studio 2014 and I don't have the permission to create tables

Possible duplicate of [TSQL Pivot without aggregate function](http://stackoverflow.com/questions/1343145/tsql-pivot-without-aggregate-function) — Tab Alleman, Jun 16 '16 at 18:36
@TabAlleman this is an UNPIVOT not a Pivot.. there's probably a duplicate out there but this is not it. — JamieD77, Jun 16 '16 at 19:40

score 2 · Answer 1 · answered Jun 16 '16 at 19:44

This is a neat trick using table valued expression

SELECT  [Name], ca.*
From    myTable
        CROSS APPLY (Values 
                (Code1, Pct1, Amt1), 
                (Code2, Pct2, Amt2), 
                (Code3, Pct3, Amt3)
        ) ca([Code#], [Pct], [Amt])

score 0 · Answer 2 · answered Jun 16 '16 at 18:45

select
    Name,
    case n when 1 then Code1 when 2 then Code2 when 3 then Code3 end as Code,
    case n when 1 then Pct1  when 2 then Pct2  when 3 then Pct3  end as Pct,
    case n when 1 then Amt1  when 2 then Amt2  when 3 then Amt3  end as Amt
from T cross join (values (1), (2), (3)) multiplier(n)

The basic idea is to triplicate the rows and then use case to pick out the correct values.

SQL transpose data

2 Answers2