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.

• Andriy M: You can probably test/adjust your query using the fiddle link provided by the OP (or at least using the setup script in that fiddle).