在 SQL Server 2008 中,我有一个包含 3 列的表 (tblStock):
零件代码 (NVARCHAR (50))库存数量 (INT)位置 (NVARCHAR(50))下面的一些示例数据:
PartCode StockQty Location……………………A 10 WHs-AB 22 WHs-AA 1 WHs-BC 20 WHs-AD 39 WHs-FE 3 WHs-DF 7 WHs-AA 9 WHs-CD 2 WHs-AF 54 WHs-E
如何创建程序以获得如下结果?
PartCode WHs-A WHs-B WHs-C WHs-D WHs-E WHs-F 合计……………………………………………………10 1 9 0 0 0 20B 22 0 0 0 0 0 22C 20 0 0 0 0 0 20D 2 0 0 0 0 39 41E 0 0 0 3 0 0 3F 7 0 0 0 54 0 61总计 61 1 9 3 54 39 167
非常感谢您的帮助,谢谢.
解决方案SAMPLE TABLE
SELECT * INTO #tblStock从(选择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')标签
获取动态旋转的列并将 NULL
替换为 zero
DECLARE @cols NVARCHAR (MAX)SELECT @cols = COALESCE (@cols + ',[' + Location + ']', '[' + Location + ']')FROM(从#tblStock 中选择不同的位置)PV按位置订购-- 因为最后一列需要 Total,所以最后追加SELECT @cols += ',[总计]'--用零替换NULL的变量声明 @NulltoZeroCols NVARCHAR (MAX)SELECT @NullToZeroCols = SUBSTRING((SELECT ',ISNULL(['+Location+'],0) AS ['+Location+']'FROM(从#tblStock 选择不同的位置)选项卡按位置排序 XML PATH('')),2,8000)SELECT @NullToZeroCols += ',ISNULL([Total],0) AS [Total]'
您可以使用 CUBE
查找行和列总计,并将 NULL
替换为 Total
用于从 CUBE.
DECLARE @query NVARCHAR(MAX)SET @query = 'SELECT PartCode,' + @NulltoZeroCols + ' FROM(选择ISNULL(CAST(PartCode AS VARCHAR(30)),''Total'')PartCode,总和(库存数量)库存数量,ISNULL(位置,''总计'')位置来自#tblStock按位置、零件代码分组带立方体) X枢(最小(库存数量)FOR 位置 IN (' + @cols + ')) pORDER BY CASE WHEN (PartCode=''Total'') THEN 1 ELSE 0 END,PartCode'执行 SP_EXECUTESQL @query
查看结果
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
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
Get the columns for dynamic pivoting and replace 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]'
You can use CUBE
to find row and column total and replace NULL
with Total
for the rows generated from 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
Click here to view result
RESULT
NOTE : If you want NULL
instead of zero
as values, use @cols
instead of @NulltoZeroCols
in dynamic pivot code
EDIT :
1. Show only Row Total
Do not use the codeSELECT @cols += ',[Total]'
and SELECT @NullToZeroCols += ',ISNULL([Total],0) AS [Total]'
.
Use ROLLUP
instead of CUBE
.
2. Show only Column Total
Use the codeSELECT @cols += ',[Total]'
and SELECT @NullToZeroCols += ',ISNULL([Total],0) AS [Total]'
.
Use ROLLUP
instead of CUBE
.
Change GROUP BY Location,PartCode
to GROUP BY PartCode,Location
.
Instead of ORDER BY CASE WHEN (PartCode=''Total'') THEN 1 ELSE 0 END,PartCode
, use WHERE PartCode<>''TOTAL'' ORDER BY PartCode
.
UPDATE : To bring PartName
for OP
I am updating the below query to add PartName
with result. Since PartName
will add extra results with CUBE
and to avoid confusion in AND
or OR
conditions, its better to join the pivoted result with the DISTINCT
values in your source table.
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
Click here to view result