sql transpose rows to columns

Question

suppose I have the following table:

+---------+------+-------------+-----+-------+--------+
| invoice | item | description | qty | price | amount |
+---------+------+-------------+-----+-------+--------+
|    1234 | L    | labour      |   1 |    50 |     50 |
|    1234 | P    | parts       |   2 |   100 |    200 |
|    9865 | L    | labour      |   1 |    25 |     25 |
|    9865 | P    | parts       |   1 |    25 |     25 |
|    5555 | P    | parts       |   2 |   100 |    200 |
+---------+------+-------------+-----+-------+--------+

I would like a select query that will transpose the 2 rows to columns for each unique invoice number such that each invoice number has only one row.

so for the above example I would expect the following:

+---------+-------+--------------+------+--------+---------+-------+--------------+------+--------+---------+
| invoice | item1 | description1 | qty1 | price1 | amount1 | item2 | description2 | qty2 | price2 | amount2 |
+---------+-------+--------------+------+--------+---------+-------+--------------+------+--------+---------+
|    1234 | L     | labour       |    1 |     50 |      50 | P     | parts        | 2    | 100    | 200     |
|    9865 | L     | labour       |    1 |     25 |      25 | P     | parts        | 1    | 25     | 25      |
|    5555 | P     | parts        |    2 |    100 |     200 | NULL  | NULL         | NULL | NULL   | NULL    |
+---------+-------+--------------+------+--------+---------+-------+--------------+------+--------+---------+

Note I'm looking for a static/predefined number of columns, not dynamic based on line items.

Answer 1

If you have a limited number of values that you want to convert into columns, then you can easily do this using row_number() with a CASE expression and an aggregate function:

select invoice,
  max(case when seq = 1 then item end) item1,
  max(case when seq = 1 then description end) description1,
  max(case when seq = 1 then qty end) qty1,
  max(case when seq = 1 then price end) price1,
  max(case when seq = 1 then amount end) amount1,
  max(case when seq = 2 then item end) item2,
  max(case when seq = 2 then description end) description2,
  max(case when seq = 2 then qty end) qty2,
  max(case when seq = 2 then price end) price2,
  max(case when seq = 2 then amount end) amount2
from 
(
  select invoice, item, description, qty, price, amount,
    row_number() over(partition by invoice order by invoice) seq
  from yourtable
) d
group by invoice;

See SQL Fiddle with Demo.

But if you want to use the PIVOT function to get the result, then you will need to look at unpivoting the multiple columns (item, description, qty, price and amount) first, then apply the pivot function. The code using PIVOT will be similar to:

;with cte as
(
  select invoice, item, description, qty, price, amount,
    row_number() over(partition by invoice order by invoice) seq
  from yourtable
)
select invoice,
  item1, description1, qty1, price1, amount1,
  item2, description2, qty2, price2, amount2
from
(
  select invoice, 
    col = col+cast(seq as varchar(10)), 
    value
  from cte
  cross apply
  (
    select 'item', item union all
    select 'description', description union all
    select 'qty', cast(qty as varchar(50)) union all
    select 'price', cast(price as varchar(50)) union all
    select 'amount', cast(amount as varchar(50))
  ) c (col, value)
) d
pivot
(
  max(value)
  for col in (item1, description1, qty1, price1, amount1,
              item2, description2, qty2, price2, amount2)
) piv;

See SQL Fiddle with Demo. Both will give a result:

| INVOICE | ITEM1 | DESCRIPTION1 | QTY1 | PRICE1 | AMOUNT1 |  ITEM2 | DESCRIPTION2 |   QTY2 | PRICE2 | AMOUNT2 |
|    1234 |     L |       labour |    1 |     50 |      50 |      P |        parts |      2 |    100 |     200 |
|    5555 |     P |        parts |    2 |    100 |     200 | (null) |       (null) | (null) | (null) |  (null) |
|    9865 |     L |       labour |    1 |     25 |      25 |      P |        parts |      1 |     25 |      25 |