In SQL Server 2008, I have a table (tblStock) with 3 columns:
- PartCode (NVARCHAR (50))
- StockQty (INT)
- Location (NVARCHAR(50))
some example data below:
PartCode StockQty Location
......... ......... .........
A 10 WHs-A
B 22 WHs-A
A 1 WHs-B
C 20 WHs-A
D 39 WHs-F
E 3 WHs-D
F 7 WHs-A
A 9 WHs-C
D 2 WHs-A
F 54 WHs-E
How to create procedure to get the result as below?
PartCode WHs-A WHs-B WHs-C WHs-D WHs-E WHs-F Total
........ ..... ..... ..... ...... ..... ..... .....
A 10 1 9 0 0 0 20
B 22 0 0 0 0 0 22
C 20 0 0 0 0 0 20
D 2 0 0 0 0 39 41
E 0 0 0 3 0 0 3
F 7 0 0 0 54 0 61
Total 61 1 9 3 54 39 167
Your help is much appreciated, thanks.
SAMPLE TABLE
Get the columns for dynamic pivoting and replace
NULL
withzero
You can use
CUBE
to find row and column total and replaceNULL
withTotal
for the rows generated fromCUBE
.RESULT
NOTE : If you want
NULL
instead ofzero
as values, use@cols
instead of@NulltoZeroCols
in dynamic pivot codeEDIT :
1. Show only Row Total
SELECT @cols += ',[Total]'
andSELECT @NullToZeroCols += ',ISNULL([Total],0) AS [Total]'
.ROLLUP
instead ofCUBE
.2. Show only Column Total
SELECT @cols += ',[Total]'
andSELECT @NullToZeroCols += ',ISNULL([Total],0) AS [Total]'
.ROLLUP
instead ofCUBE
.GROUP BY Location,PartCode
toGROUP BY PartCode,Location
.ORDER BY CASE WHEN (PartCode=''Total'') THEN 1 ELSE 0 END,PartCode
, useWHERE PartCode<>''TOTAL'' ORDER BY PartCode
.UPDATE : To bring
PartName
for OPI am updating the below query to add
PartName
with result. SincePartName
will add extra results withCUBE
and to avoid confusion inAND
orOR
conditions, its better to join the pivoted result with theDISTINCT
values in your source table.you need to use
case based aggregation
to pivot the dataTo get the
total
row useunion
In case the
Location
values are not known in advance, you need to construct adynamic query
you can also use
pivot
keyword to do the same.