SQL Pivot insertion

Question

I have a table, lets call it tblINVOICE. The invoice can hold one or more item and for each item on the invoice, a line is created.

+-----------------------------------------+
| InvNo  | ItemNo | ItemPrice | VatAmount |
+-----------------------------------------+
| 001    | A001   | 100.00    | 10.00     |
| 001    | B020   | 233.33    | 23.00     |
| 001    | D111   | 20.99     | 2.00      |
| 002    | B020   | 233.33    | 23.00     |
| 002    | X901   | 108.00    | 10.80     |
+-----------------------------------------+

Now I want to insert these data into another table, whereby each line represent an invoice, as below;

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| InvNo  | Item1 | Item2 | Item3 | Item4 | Item5 | ItemPrice1 | ItemPrice2 | ItemPrice3 | ItemPrice4 | ItemPrice5 | VatAmount1 | VatAmount2 | VatAmount3 | VatAmount4 | VatAmount5 |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 001    | A001  | B020  | D111  | Null  | Null  | 100.00     | 233.00     |  20.99     | Null       | Null       | 10.00      | 23.00      |  2.00      | 23.00      | 10.80      |
| 002    | B020  | X901  | Null  | Null  | Null  | 233.33     |  23.00     |  Null      | Null       | Null       | 23.00      | 10.80      |  Null      | Null       | Null       |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

I want all the rows to be pivoted into columns. How do I proceed? Is there a simple way to do this? Or should I loop through the 1st table and insert into the second?

Thanks

Answer 1

1. Statically, when you always want five columns:

---

CREATE TABLE #inv(id INT IDENTITY(1,1) PRIMARY KEY,InvNo VARCHAR(16),ItemNo VARCHAR(16), ItemPrice DECIMAL(28,2), VatAmount DECIMAL(28,2));
INSERT INTO #inv(InvNo,ItemNo,ItemPrice,VatAmount)VALUES
    ('001','A001',100.00,10.00),
    ('001','B020',233.33,23.00),
    ('001','D111',20.99,2.00),
    ('002','B020',233.33,23.00),
    ('002','X901',108.00,10.80);

SELECT
    InvNo,
    Item1=MAX(CASE WHEN ItemId=1 THEN ItemNo END),
    Item2=MAX(CASE WHEN ItemId=2 THEN ItemNo END),
    Item3=MAX(CASE WHEN ItemId=3 THEN ItemNo END),
    Item4=MAX(CASE WHEN ItemId=4 THEN ItemNo END),
    Item5=MAX(CASE WHEN ItemId=5 THEN ItemNo END),
    ItemPrice1=MAX(CASE WHEN ItemId=1 THEN ItemPrice END),
    ItemPrice2=MAX(CASE WHEN ItemId=2 THEN ItemPrice END),
    ItemPrice3=MAX(CASE WHEN ItemId=3 THEN ItemPrice END),
    ItemPrice4=MAX(CASE WHEN ItemId=4 THEN ItemPrice END),
    ItemPrice5=MAX(CASE WHEN ItemId=5 THEN ItemPrice END),
    VatAmount1=MAX(CASE WHEN ItemId=1 THEN VatAmount END),
    VatAmount2=MAX(CASE WHEN ItemId=2 THEN VatAmount END),
    VatAmount3=MAX(CASE WHEN ItemId=3 THEN VatAmount END),
    VatAmount4=MAX(CASE WHEN ItemId=4 THEN VatAmount END),
    VatAmount5=MAX(CASE WHEN ItemId=5 THEN VatAmount END)
FROM
    (
        SELECT
            *,
            ItemId=ROW_NUMBER() OVER (PARTITION BY InvNo ORDER BY id)
        FROM
            #inv
    ) AS inv_nr
GROUP BY 
    InvNo;

DROP TABLE #inv;

Results:

+-------+-------+-------+-------+-------+-------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+
| InvNo | Item1 | Item2 | Item3 | Item4 | Item5 | ItemPrice1 | ItemPrice2 | ItemPrice3 | ItemPrice4 | ItemPrice5 | VatAmount1 | VatAmount2 | VatAmount3 | VatAmount4 | VatAmount5 |
+-------+-------+-------+-------+-------+-------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+
|   001 | A001  | B020  | D111  | NULL  | NULL  | 100.00     | 233.33     | 20.99      | NULL       | NULL       | 10.00      | 23.00      | 2.00       | NULL       | NULL       |
|   002 | B020  | X901  | NULL  | NULL  | NULL  | 233.33     | 108.00     | NULL       | NULL       | NULL       | 23.00      | 10.80      | NULL       | NULL       | NULL       |
+-------+-------+-------+-------+-------+-------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+

---

2. Dynamically, when you want an amount of columns equal to the maximum number of rows for any InvNo

---

CREATE TABLE #inv(id INT IDENTITY(1,1) PRIMARY KEY,InvNo VARCHAR(16),ItemNo VARCHAR(16), ItemPrice DECIMAL(28,2), VatAmount DECIMAL(28,2));
INSERT INTO #inv(InvNo,ItemNo,ItemPrice,VatAmount)VALUES
    ('001','A001',100.00,10.00),
    ('001','B020',233.33,23.00),
    ('001','D111',20.99,2.00),
    ('002','B020',233.33,23.00),
    ('002','X901',108.00,10.80);

DECLARE @item_cols NVARCHAR(MAX)=STUFF((
    SELECT DISTINCT
        ',Item'+CAST(ROW_NUMBER() OVER (PARTITION BY InvNo ORDER BY id) AS VARCHAR(16))+'=MAX(CASE WHEN row_id='+CAST(ROW_NUMBER() OVER (PARTITION BY InvNo ORDER BY id) AS VARCHAR(16))+' THEN ItemNo END)'
    FROM
        #inv
    FOR
        XML PATH('')
    ),1,1,''
);
DECLARE @price_cols NVARCHAR(MAX)=STUFF((
    SELECT DISTINCT
        ',ItemPrice'+CAST(ROW_NUMBER() OVER (PARTITION BY InvNo ORDER BY id) AS VARCHAR(16))+'=MAX(CASE WHEN row_id='+CAST(ROW_NUMBER() OVER (PARTITION BY InvNo ORDER BY id) AS VARCHAR(16))+' THEN ItemPrice END)'
    FROM
        #inv
    FOR
        XML PATH('')
    ),1,1,''
);
DECLARE @vat_cols NVARCHAR(MAX)=STUFF((
    SELECT DISTINCT
        ',VatAmount'+CAST(ROW_NUMBER() OVER (PARTITION BY InvNo ORDER BY id) AS VARCHAR(16))+'=MAX(CASE WHEN row_id='+CAST(ROW_NUMBER() OVER (PARTITION BY InvNo ORDER BY id) AS VARCHAR(16))+' THEN VatAmount END)'
    FROM
        #inv
    FOR
        XML PATH('')
    ),1,1,''
);

DECLARE @stmt NVARCHAR(MAX)=N'
    SELECT
        InvNo,' + @item_cols + ',' + @price_cols + ',' + @vat_cols + ' 
    FROM
        (
            SELECT
                *,
                row_id=ROW_NUMBER() OVER (PARTITION BY InvNo ORDER BY id)
            FROM
                #inv
        ) AS inv_nr
    GROUP BY 
        InvNo;
';
EXECUTE sp_executesql @stmt;

DROP TABLE #inv;

Results:

+-------+-------+-------+-------+------------+------------+------------+------------+------------+------------+
| InvNo | Item1 | Item2 | Item3 | ItemPrice1 | ItemPrice2 | ItemPrice3 | VatAmount1 | VatAmount2 | VatAmount3 |
+-------+-------+-------+-------+------------+------------+------------+------------+------------+------------+
|   001 | A001  | B020  | D111  | 100.00     | 233.33     | 20.99      | 10.00      | 23.00      | 2.00       |
|   002 | B020  | X901  | NULL  | 233.33     | 108.00     | NULL       | 23.00      | 10.80      | NULL       |
+-------+-------+-------+-------+------------+------------+------------+------------+------------+------------+