如何使用存储过程在 SQL Server 中透视表?

问题描述

原始输出:

所需的输出:

推荐答案

检查下面的示例以供您参考，或者发布您当前的输出和所需的输出.

Check this below example for your reference or else post your present output and required output.

/*

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

REQUIRED OUT PUT

+-------------+-------+-------+-------+-------+-------+-------+-------+
|  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 |
| GRAND_TOTAL |    61 |     1 |     9 |     3 |    54 |    39 |   167 |
+-------------+-------+-------+-------+-------+-------+-------+-------+

*/
DECLARE @COLUMNS NVARCHAR(MAX),@COLUMNS_ISNULL NVARCHAR(MAX),@SQLQUERY NVARCHAR(MAX),@COLUMNS_TOTAL NVARCHAR(MAX),
@GRNADTOTAL NVARCHAR(MAX),@GRNADTOTAL_SUM NVARCHAR(MAX)

 SET @COLUMNS= stuff((SELECT distinct ',['+location +']' FROM #tblstock for xml path('')),1,1,'')

SET @COLUMNS_ISNULL=  stuff((SELECT distinct ',ISNULL(['+location +'],0) AS ['+location +']'   FROM #tblstock for xml path('')),1,1,'')


SET @COLUMNS_TOTAL=stuff((SELECT distinct '+ISNULL(['+location +'],0)'   FROM #tblstock for xml path('')),1,1,'')


--SET @COLUMNS_TOTAL=','+@COLUMNS_TOTAL+'as total '

SET @GRNADTOTAL= stuff((SELECT distinct ',SUM(ISNULL(['+location +'],0))'   FROM #tblstock for xml path('')),1,1,'')

set @GRNADTOTAL_SUM='SUM('+@COLUMNS_TOTAL+')'


select @COLUMNS_TOTAL


SET @SQLQUERY=
N'
SELECT partcode, '+@COLUMNS_ISNULL +',' +@COLUMNS_TOTAL +N'as total FROM(
SELECT * FROM #TBLSTOCK
)AS A
PIVOT (MAX(STOCKQTY) FOR LOCATION IN('+@COLUMNS+N')
) AS A'

PRINT @SQLQUERY

EXEC SP_EXECUTESQL @SQLQUERY




SET @SQLQUERY=
N'
;with cte as(
SELECT partcode, '+@COLUMNS_ISNULL +',' +@COLUMNS_TOTAL +N'as total FROM(
SELECT * FROM #TBLSTOCK
)AS A
PIVOT (MAX(STOCKQTY) FOR LOCATION IN('+@COLUMNS+N')
) AS A
)
select PARTCODE, '+@COLUMNS_ISNULL+',' +@COLUMNS_TOTAL +N'AS TOTAL from cte
union all
select ''GRAND_TOTAL'', '+@GRNADTOTAL+','+ @GRNADTOTAL_SUM+N'from cte'

PRINT @SQLQUERY

EXEC SP_EXECUTESQL @SQLQUERY

这篇关于如何使用存储过程在 SQL Server 中透视表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！