在sql Server 2008中,我有一个包含3列的表(tblStock)：
- PartCode (NVARCHAR (50))
- StockQty (INT)
- Location (NVARCHAR(50))
下面的一些示例数据：
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
如何创建程序以获得如下结果？
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
非常感谢您的帮助,谢谢.
解决方法
样本表
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,9,'WHs-C' UNION ALL SELECT 'D',2,'WHs-A' UNION ALL SELECT 'F',54,'WHs-E' )TAB
获取用于动态旋转的列并将零替换为NULL
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('')),8000)
SELECT @NullToZeroCols += ',ISNULL([Total],0) AS [Total]'
您可以使用CUBE查找行和列总计,并将CUBE生成的行替换为NULL.
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
> Click here查看结果
结果
注意：如果您想要NULL而不是零作为值,请在动态数据透视代码中使用@cols而不是@NulltoZeroCols
编辑：
1.仅显示行总计
>不要使用代码SELECT @cols =’,[Total]’和SELECT @NullToZeroCols =’,0)AS [Total]’.
>使用ROLLUP而不是CUBE.
2.仅显示列总计
>使用代码SELECT @cols =’,0)AS [Total]’.
>使用ROLLUP而不是CUBE.
>将GROUP BY位置,PartCode更改为GROUP BY PartCode,Location.
>而不是ORDER BY CASE WHEN(PartCode =”Total”)THEN 1 ELSE 0 END,PartCode,使用WHERE PartCode<>”TOTAL”按PartCode排序.
更新：为OP带来PartName
我正在更新以下查询以添加带有结果的PartName.由于PartName将使用CUBE添加额外的结果并避免在AND或OR条件中出现混淆,因此最好将轮转结果与源表中的DISTINCT值相结合.
DECLARE @query NVARCHAR(MAX)
SET @query = 'SELECT P.PartCode,T.PartName,' + @NulltoZeroCols + ' FROM
(
SELECT
ISNULL(CAST(PartCode AS VARCHAR(30)),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
> Click here查看结果
