SQL Server : Pivot and unpivots

Question

I'm trying to pivot this SQL Server table, however I'm struggling to wrap my head around what I need to do. I can do a basic pivot, however this has become very challenging. I am in essence to convert the table below to

Company Code    Account Class           Period  Billings Collections    Debtors
--------------------------------------------------------------------------------
500             Accounts Receiveable    1       xx           xx         xx
500             Accounts Receiveable    2       xxx          xx         xx
500             Accounts Receiveable    3       xx           xx         xxx
500             Accounts Receiveable    1       xx           xx         xx

to

Company Code       Account Class                              1  2   3
--------------------------------------------------------------------------
500                Accounts Receiveable       Billings       xx xx  xx
500                Accounts Receiveable       Collections   xxx xx  xx
500                Accounts Receiveable       Debtors       xx  xx  xxx

I was planning to to a pivot and on a single column and then join the table with itself, performing another pivot again with itself but I don't capture the column name. Any suggestions would be much appreciated.

Answer 1

Please try the following. First you need to UNPIVOT Billings, Collections, and Debtors then PIVOT Period.

declare @table table
    ([CompanyCode] int, [AccountClass] varchar(20), [Period] int, [Billings] varchar(3), [Collections] varchar(3), [Debtors] varchar(3));

INSERT INTO @table
    ([CompanyCode], [AccountClass], [Period], [Billings], [Collections], [Debtors])
VALUES
    (500, 'Accounts Receiveable', 1, 'xx', 'xx', 'xx'),
    (500, 'Accounts Receiveable', 2, 'xxx', 'xx', 'xx'),
    (500, 'Accounts Receiveable', 3, 'xx', 'xx', 'xxx'),
    (500, 'Accounts Receiveable', 1, 'xx', 'xx', 'xx');

SELECT *
  FROM (
         SELECT [CompanyCode], [AccountClass], [Period], [Type], [Value]
         FROM @table
         UNPIVOT ([Value] FOR [Type] IN ([Billings],[Collections],[Debtors])) AS u
       ) as t
  PIVOT (MAX(Value) FOR [Period] IN ([1], [2], [3])) AS pvt;
  -- use SUM(Value) if Billings/Collections/Debtors are numeric columns

---

OUTPUT

CompanyCode AccountClass         Type         1    2    3
----------- -------------------- ------------ ---- ---- ----
500         Accounts Receiveable Billings     xx   xxx  xx
500         Accounts Receiveable Collections  xx   xx   xx
500         Accounts Receiveable Debtors      xx   xx   xxx

Answer 2

Your thinking is correct that you'll need to unpivot first in order to get the final result that you want. You'll need to convert your multiple columns of Billings, Collections and Debtors into multiple rows and then pivot the Period values into columns.

You didn't specify what version of SQL Server you are using, but starting in SQL Server 2005 you can use CROSS APPLY to unpivot:

select 
  CompanyCode,
  AccountClass,
  period,
  [Type],
  Value
from yourtable t
cross apply
(
  select 'Billings', Billings union all
  select 'Collections', Collections union all
  select 'Debtors', Debtors
) c ([Type], value);

See Demo. This turns your data into the format:

| COMPANYCODE |         ACCOUNTCLASS | PERIOD |        TYPE | VALUE |
|-------------|----------------------|--------|-------------|-------|
|         500 | Accounts Receiveable |      1 |    Billings |    xx |
|         500 | Accounts Receiveable |      1 | Collections |    xx |
|         500 | Accounts Receiveable |      1 |     Debtors |    xx |
|         500 | Accounts Receiveable |      2 |    Billings |   xxx |
|         500 | Accounts Receiveable |      2 | Collections |    xx |

You'll notice that you now have a row for each Billings, Collections and Debtors. Now you can apply the PIVOT function to the Period column:

select 
  CompanyCode,
  AccountClass,
  Type,
  [1], 
  [2],
  [3]
from
(
  select 
    CompanyCode,
    AccountClass,
    period,
    [Type],
    Value
  from yourtable t
  cross apply
  (
    select 'Billings', Billings union all
    select 'Collections', Collections union all
    select 'Debtors', Debtors
  ) c ([Type], value)
) unp
pivot
(
  max(value)
  for period in ([1], [2], [3])
) piv;

See SQL Fiddle with Demo. This gives you a result of:

| COMPANYCODE |         ACCOUNTCLASS |        TYPE |  1 |   2 |   3 |
|-------------|----------------------|-------------|----|-----|-----|
|         500 | Accounts Receiveable |    Billings | xx | xxx |  xx |
|         500 | Accounts Receiveable | Collections | xx |  xx |  xx |
|         500 | Accounts Receiveable |     Debtors | xx |  xx | xxx |

Now if you have the possibility that the CompanyCode and AccountClass could appear multiple times for the same Period, you'll need to create a value that can be used to return multiple distinct rows. In this case, you'd need to use a windowing function similar to row_number() to create a unique sequence for these combinations. I'd modify the code above slightly to:

select 
  CompanyCode,
  AccountClass,
  Type,
  [1], 
  [2],
  [3]
from
(
  select 
    CompanyCode,
    AccountClass,
    seq,
    period,
    [Type],
    Value
  from
  (
    select CompanyCode, AccountClass, Period, Billings, 
      Collections, Debtors,
      seq = row_number() over(partition by CompanyCode, AccountClass, Period
                              order by CompanyCode, AccountClass) 
    from yourtable
  ) t
  cross apply
  (
    select 'Billings', Billings union all
    select 'Collections', Collections union all
    select 'Debtors', Debtors
  ) c ([Type], value)
) unp
pivot
(
  max(value)
  for period in ([1], [2], [3])
) piv;

See SQL Fiddle with Demo. You'll notice that this changes the result slightly to:

| COMPANYCODE |         ACCOUNTCLASS |        TYPE |  1 |      2 |      3 |
|-------------|----------------------|-------------|----|--------|--------|
|         500 | Accounts Receiveable |    Billings | xx |    xxx |     xx |
|         500 | Accounts Receiveable | Collections | xx |     xx |     xx |
|         500 | Accounts Receiveable |     Debtors | xx |     xx |    xxx |
|         500 | Accounts Receiveable |    Billings | xx | (null) | (null) |
|         500 | Accounts Receiveable | Collections | xx | (null) | (null) |
|         500 | Accounts Receiveable |     Debtors | xx | (null) | (null) |