Sql Pivot Insertion

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. +-----------------------------------------+ | I

Solution 1:

1. Statically, when you always want five columns:

---

CREATETABLE #inv(id INTIDENTITY(1,1) PRIMARY KEY,InvNo VARCHAR(16),ItemNo VARCHAR(16), ItemPrice DECIMAL(28,2), VatAmount DECIMAL(28,2));
INSERTINTO #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(CASEWHEN ItemId=1THEN ItemNo END),
    Item2=MAX(CASEWHEN ItemId=2THEN ItemNo END),
    Item3=MAX(CASEWHEN ItemId=3THEN ItemNo END),
    Item4=MAX(CASEWHEN ItemId=4THEN ItemNo END),
    Item5=MAX(CASEWHEN ItemId=5THEN ItemNo END),
    ItemPrice1=MAX(CASEWHEN ItemId=1THEN ItemPrice END),
    ItemPrice2=MAX(CASEWHEN ItemId=2THEN ItemPrice END),
    ItemPrice3=MAX(CASEWHEN ItemId=3THEN ItemPrice END),
    ItemPrice4=MAX(CASEWHEN ItemId=4THEN ItemPrice END),
    ItemPrice5=MAX(CASEWHEN ItemId=5THEN ItemPrice END),
    VatAmount1=MAX(CASEWHEN ItemId=1THEN VatAmount END),
    VatAmount2=MAX(CASEWHEN ItemId=2THEN VatAmount END),
    VatAmount3=MAX(CASEWHEN ItemId=3THEN VatAmount END),
    VatAmount4=MAX(CASEWHEN ItemId=4THEN VatAmount END),
    VatAmount5=MAX(CASEWHEN ItemId=5THEN VatAmount END)
FROM
    (
        SELECT*,
            ItemId=ROW_NUMBER() OVER (PARTITIONBY InvNo ORDERBY id)
        FROM
            #inv
    ) AS inv_nr
GROUPBY 
    InvNo;

DROPTABLE #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

---

CREATETABLE #inv(id INTIDENTITY(1,1) PRIMARY KEY,InvNo VARCHAR(16),ItemNo VARCHAR(16), ItemPrice DECIMAL(28,2), VatAmount DECIMAL(28,2));
INSERTINTO #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((
    SELECTDISTINCT',Item'+CAST(ROW_NUMBER() OVER (PARTITIONBY InvNo ORDERBY id) ASVARCHAR(16))+'=MAX(CASE WHEN row_id='+CAST(ROW_NUMBER() OVER (PARTITIONBY InvNo ORDERBY id) ASVARCHAR(16))+' THEN ItemNo END)'FROM
        #inv
    FOR
        XML PATH('')
    ),1,1,''
);
DECLARE@price_cols NVARCHAR(MAX)=STUFF((
    SELECTDISTINCT',ItemPrice'+CAST(ROW_NUMBER() OVER (PARTITIONBY InvNo ORDERBY id) ASVARCHAR(16))+'=MAX(CASE WHEN row_id='+CAST(ROW_NUMBER() OVER (PARTITIONBY InvNo ORDERBY id) ASVARCHAR(16))+' THEN ItemPrice END)'FROM
        #inv
    FOR
        XML PATH('')
    ),1,1,''
);
DECLARE@vat_cols NVARCHAR(MAX)=STUFF((
    SELECTDISTINCT',VatAmount'+CAST(ROW_NUMBER() OVER (PARTITIONBY InvNo ORDERBY id) ASVARCHAR(16))+'=MAX(CASE WHEN row_id='+CAST(ROW_NUMBER() OVER (PARTITIONBY InvNo ORDERBY id) ASVARCHAR(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;

DROPTABLE #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|+-------+-------+-------+-------+------------+------------+------------+------------+------------+------------+