How to PIVOT Column Headers - SQL Data

Question

I have a data set view that needs to be pivoted whereby the Column Headers for AMC1, AMC2, and AMC3 Will become Rows under LOB. Here is the example of Orginal:

 Qtr    Year    BU  BU_Description  P2A_Line       AMC1  AMC2   AMC3
 Q3 2013    B52100  52100 - UFC      L1_1          203    17     7
 Q3 2013    B52100  52100 - UFC      L1_2          -      -      -   
 Q3 2013    B52100  52100 - UFC      L1_3          123    113    -   

Here is the example of the End Result:

 Qtr    Year    BU  BU_Description   LOB     L1_1   L1_2    PL1_3
  Q3    2013    B52100  52100 - UFC  AMC1    203     -       123 
  Q3    2013    B52100  52100 - UFC  AMC2    17      -       113 
  Q3    2013    B52100  52100 - UFC  AMC3    7       -       -   

Does anyone have a simplistic and relatively easy way to do this? The examples out there have too many variables to derive the answer. Any help would be greatly appreciated?

Answer 1

There are several different ways that you can get the result, by using an aggregate function with a CASE expression, using the PIVOT function - but before you implement those you will first need to look at unpivoting your multiple columns of data - AMC1, AMC2, and AMC3.

The process of unpivoting converts your multiple columns into rows. You can use the unpivot function or you can use CROSS APPLY to convert the data:

select qtr, year, bu, bu_description, p2a_line,
  lob, value
from yourtable
cross apply
(
  select 'amc1', amc1 union all
  select 'amc2', amc2 union all
  select 'amc3', amc3
) c (lob, value);

See SQL Fiddle with Demo. Once the data is in this format then you can convert your P2A_Line values into columns.

Using an aggregate function with a CASE expression the query will be:

select qtr, year, bu, bu_description,
  lob, 
  max(case when p2a_line = 'L1_1' then value end) L1_1,
  max(case when p2a_line = 'L1_2' then value end) L1_2,
  max(case when p2a_line = 'L1_3' then value end) L1_3
from 
(
  select qtr, year, bu, bu_description, p2a_line,
    lob, value
  from yourtable
  cross apply
  (
    select 'amc1', amc1 union all
    select 'amc2', amc2 union all
    select 'amc3', amc3
  ) c (lob, value)
) d
group by qtr, year, bu, bu_description, lob;

See SQL Fiddle with Demo.

In SQL Server 2005+, you can use the PIVOT function:

select qtr, year, bu, bu_description,
  lob, L1_1, L1_2, L1_3
from 
(
  select qtr, year, bu, bu_description, p2a_line,
    lob, value
  from yourtable
  cross apply
  (
    select 'amc1', amc1 union all
    select 'amc2', amc2 union all
    select 'amc3', amc3
  ) c (lob, value)
) d
pivot
(
  max(value)
  for p2a_line in (L1_1, L1_2, L1_3)
) piv;

See SQL Fiddle with Demo.

Finally if you had an unknown number of p2a_line values, then you could use dynamic SQL:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT  ',' + QUOTENAME(P2A_Line) 
                    from yourtable
                    group by P2A_Line
                    order by P2A_Line
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = N'SELECT qtr, year, bu, bu_description, lob,' + @cols + N' 
            from 
            (
              select qtr, year, bu, bu_description, p2a_line,
                lob, value
              from yourtable
              cross apply
              (
                select ''amc1'', amc1 union all
                select ''amc2'', amc2 union all
                select ''amc3'', amc3
              ) c (lob, value)
            ) x
            pivot 
            (
                max(value)
                for p2a_line in (' + @cols + N')
            ) p '

execute sp_executesql @query;

See SQL Fiddle with Demo

All versions give the result:

| QTR | YEAR |     BU | BU_DESCRIPTION |  LOB | L1_1 |   L1_2 |   L1_3 |
|-----|------|--------|----------------|------|------|--------|--------|
|  Q3 | 2013 | B52100 |    52100 - UFC | amc1 |  203 | (null) |    123 |
|  Q3 | 2013 | B52100 |    52100 - UFC | amc2 |   17 | (null) |    113 |
|  Q3 | 2013 | B52100 |    52100 - UFC | amc3 |    7 | (null) | (null) |