Select with dynamic columns

Question

I have an InvoiceHeader, InvoiceItem tables

InvoiceHeader content:

InvoiceId   InvoiceNumber   BusinessId  BusinessName    BranchId
        1               1           10       BSN INC    1
        2               2           10       BSN INC    2
        3               3           23    SAMPLE INC    1
        4               4           45   ANOTHER INC    1
        5               5           45   ANOTHER INC    3

InvoiceItem content:

InvoiceId   ItemId  ItemCode    Name            Quantity
        1        1    SNACK1    GOOD SNACKS     150
        1        2    SNACK2    SNACKERS        120
        2        1    SNACK1    GOOD SNACKS     100
        2        2    SNACK2    SNACKERS        150
        3        1    SNACK1    GOOD SNACKS     150
        3        2    SNACK2    SNACKERS        150
        3        3    SNACK3    BIG SNACKS      110
        4        1    SNACK1    GOOD SNACKS     90
        4        2    SNACK2    SNACKERS        80
        4        3    SNACK3    BIG SNACKS      120
        5        1    SNACK1    GOOD SNACKS     50
        5        2    SNACK2    SNACKERS        70
        5        3    SNACK3    BIG SNACKS      90
        5        4    SNACK4    TASTY SNACKS    70

Here is the sqlfiddle: http://sqlfiddle.com/#!18/aa5fa/6

Now the result that I want is like this:

InvoiceNumber   BusinessName    BusinnessBranch SNACK1  SNACK2  SNACK3  SNACK4  TotalItems
    1           BSN INC         Branch1         150     120     0       0       270
    2           BSN INC         Branch2         100     150     0       0       250
    3           SAMPLE INC      Branch1         150     150     110     0       410
    4           ANOTHER INC     Branch1         90      80      120     0       290
    5           ANOTHER INC     Branch3         50      70      90      70      280
                                                                                1500 

As you can see the items code from the invoices are added as columns to count the items, in the last column the items total from that invoice, and the last row it shows the total items from all selected invoices.

The number of transposed columns can be more than that, can reach even more than 10.

Answer 1

This query will give you what you need, or at least get you close.

;WITH CTE_Items AS
  (
    SELECT InvoiceId
        , COALESCE(SNACK1, 0) AS SNACK1
        , COALESCE(SNACK2, 0) AS SNACK2
        , COALESCE(SNACK3, 0) AS SNACK3
        , COALESCE(SNACK4, 0) AS SNACK4
    FROM (SELECT InvoiceId, ItemCode, Quantity FROM dbo.InvoiceItem AS II) AS I
      PIVOT (SUM(Quantity) FOR ItemCode IN ([SNACK1], [SNACK2], [SNACK3], [SNACK4])) AS P
    )
SELECT IH.InvoiceId
    , IH.BusinessName
    , 'Branch' + CONVERT(VARCHAR(10), IH.BranchID) AS BusinessBranch
    , I.SNACK1
    , I.SNACK2
    , I.SNACK3
    , I.SNACK4
    , TotalSnacks = I.SNACK1 + I.SNACK2 + I.SNACK3 + I.SNACK4
FROM dbo.InvoiceHeader AS IH
    LEFT OUTER JOIN CTE_Items AS I ON I.InvoiceID = IH.InvoiceID

Answer 2

WITH abc AS (
SELECT
   i.InvoiceId
   ,h.BusinessName
   ,h.BranchId
   ,ItemCode
   ,Quantity
FROM InvoiceItem i
INNER JOIN InvoiceHeader h ON i.InvoiceId = h.InvoiceId
)
SELECT PT.InvoiceId, PT.BusinessName, PT.BranchId,
    ISNULL(PT.SNACK1,0) as Snack1,
    ISNULL(PT.SNACK2,0) as Snack2,
    ISNULL(PT.SNACK3,0) as Snack3,
    ISNULL(PT.SNACK4,0) as Snack4
FROM abc
PIVOT( SUM(Quantity)
       FOR ItemCode in ("SNACK1","SNACK2","SNACK3","SNACK4")
       ) as PT

output:

SQLfiddle

| InvoiceId | BusinessName | BranchId | Snack1 | Snack2 | Snack3 | Snack4 |
|-----------|--------------|----------|--------|--------|--------|--------|
|         1 |      BSN INC |        1 |    150 |    120 |      0 |      0 |
|         2 |      BSN INC |        2 |    100 |    150 |      0 |      0 |
|         3 |   SAMPLE INC |        1 |    150 |    150 |    110 |      0 |
|         4 |  ANOTHER INC |        1 |     90 |     80 |    120 |      0 |
|         5 |  ANOTHER INC |        3 |     50 |     70 |     90 |     70 |

Answer 3

I've had to PIVOT a fair bit with the reports I run. However adding unknown columns in the mix always has me using dynamic SQL to get it running, and another query for the column names.

DECLARE @column nvarchar(MAX) = N'',
        @sql    nvarchar(MAX) = N''

SELECT  @column += ', [' + ItemCode + ']'
FROM    InvoiceItem
GROUP BY ItemCode

SET @sql = '
    SELECT    InvoiceNumber,
              BusinessName,
              ' + STUFF(@column, 1, 2, '') + '
    FROM (
        SELECT    InvoiceNumber,
                  BusinessName,
                  Quantity,
                  ItemCode
        FROM      InvoiceItem i
        LEFT JOIN InvoiceHeader h
             ON   i.InvoiceId = h.InvoiceId
    ) AS z
    PIVOT
    (
        SUM(Quantity) FOR ItemCode IN (' + STUFF(@column, 1, 2, '') + ')
    ) AS pvt;'

    EXEC sp_executesql @sql;

I think that's it. I adapted it from a PIVOT I use but may have fluffed up all the names when transposing them all. The above allows you to run for as many ItemCodes you have without having to hard code anything. The only way to do that is with dynamic SQL unless you want to change the query when the data changes.

Let me know if it works, if I have more time later I'll create a copy of the data to get a tested version.