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.