样本表
SELECT * INTO #tblStock
FROM
(
SELECT 'A' PartCode, 10 StockQty, 'WHs-A' Location
UNION ALL
SELECT 'B', 22, 'WHs-A'
UNION ALL
SELECT 'A', 1, 'WHs-B'
UNION ALL
SELECT 'C', 20, 'WHs-A'
UNION ALL
SELECT 'D', 39, 'WHs-F'
UNION ALL
SELECT 'E', 3, 'WHs-D'
UNION ALL
SELECT 'F', 7, 'WHs-A'
UNION ALL
SELECT 'A', 9, 'WHs-C'
UNION ALL
SELECT 'D', 2, 'WHs-A'
UNION ALL
SELECT 'F', 54, 'WHs-E'
)TAB
获取动态旋转的列并替换NULL
with zero
DECLARE @cols NVARCHAR (MAX)
SELECT @cols = COALESCE (@cols + ',[' + Location + ']', '[' + Location + ']')
FROM (SELECT DISTINCT Location FROM #tblStock) PV
ORDER BY Location
-- Since we need Total in last column, we append it at last
SELECT @cols += ',[Total]'
--Varible to replace NULL with zero
DECLARE @NulltoZeroCols NVARCHAR (MAX)
SELECT @NullToZeroCols = SUBSTRING((SELECT ',ISNULL(['+Location+'],0) AS ['+Location+']'
FROM (SELECT DISTINCT Location FROM #tblStock)TAB
ORDER BY Location FOR XML PATH('')),2,8000)
SELECT @NullToZeroCols += ',ISNULL([Total],0) AS [Total]'
您可以使用CUBE
查找行和列总计并替换NULL
with Total
对于生成的行CUBE
。
DECLARE @query NVARCHAR(MAX)
SET @query = 'SELECT PartCode,' + @NulltoZeroCols + ' FROM
(
SELECT
ISNULL(CAST(PartCode AS VARCHAR(30)),''Total'')PartCode,
SUM(StockQty)StockQty ,
ISNULL(Location,''Total'')Location
FROM #tblStock
GROUP BY Location,PartCode
WITH CUBE
) x
PIVOT
(
MIN(StockQty)
FOR Location IN (' + @cols + ')
) p
ORDER BY CASE WHEN (PartCode=''Total'') THEN 1 ELSE 0 END,PartCode'
EXEC SP_EXECUTESQL @query
RESULT
注意:如果你愿意NULL
代替zero
作为值,使用@cols
代替@NulltoZeroCols
在动态枢轴代码中
EDIT :
1.仅显示行总计
- 不要使用代码
SELECT @cols += ',[Total]'
and SELECT @NullToZeroCols += ',ISNULL([Total],0) AS [Total]'
.
- Use
ROLLUP
代替CUBE
.
2.仅显示列总计
- 使用代码
SELECT @cols += ',[Total]'
and SELECT @NullToZeroCols += ',ISNULL([Total],0) AS [Total]'
.
- Use
ROLLUP
代替CUBE
.
- Change
GROUP BY Location,PartCode
to GROUP BY PartCode,Location
.
- 代替
ORDER BY CASE WHEN (PartCode=''Total'') THEN 1 ELSE 0 END,PartCode
, use WHERE PartCode<>''TOTAL'' ORDER BY PartCode
.
更新:带来PartName
for OP
我正在更新以下查询以添加PartName
与结果。自从PartName
将添加额外的结果CUBE
并避免混淆AND
or OR
条件下,最好将旋转结果与DISTINCT
源表中的值。
DECLARE @query NVARCHAR(MAX)
SET @query = 'SELECT P.PartCode,T.PartName,' + @NulltoZeroCols + ' FROM
(
SELECT
ISNULL(CAST(PartCode AS VARCHAR(30)),''Total'')PartCode,
SUM(StockQty)StockQty ,
ISNULL(Location,''Total'')Location
FROM #tblStock
GROUP BY Location,PartCode
WITH CUBE
) x
PIVOT
(
MIN(StockQty)
FOR Location IN (' + @cols + ')
) p
LEFT JOIN
(
SELECT DISTINCT PartCode,PartName
FROM #tblStock
)T
ON P.PartCode=T.PartCode
ORDER BY CASE WHEN (P.PartCode=''Total'') THEN 1 ELSE 0 END,P.PartCode'
EXEC SP_EXECUTESQL @query