SqlServer数据库使用UNPIVOT函数将多行多列转一行多列方法

1.原表数据如下：

 

 2.转换后效果：

 

 3.实现代码如下：

 1 -- 创建示例表
 2 CREATE TABLE #SalesByQuarter (
 3     rowNum INT,
 4     Q1 DECIMAL(10, 2),
 5     Q2 DECIMAL(10, 2),
 6     Q3 DECIMAL(10, 2),
 7     Q4 DECIMAL(10, 2)
 8 );
 9 
10 -- 插入示例数据
11 INSERT INTO #SalesByQuarter (rowNum,Q1, Q2, Q3, Q4)
12 VALUES
13 (1,1000.00, 1200.00, 1300.00, 1500.00),
14 (2,800.00, 900.00, 1100.00, 1400.00),
15 (3,600.00, 650.00, 1500.00, 1300.00)
16 
17 
18 -- 声明变量用于动态 SQL
19 DECLARE @Columns NVARCHAR(MAX) = '';
20 DECLARE @DynamicSQL NVARCHAR(MAX);
21 
22 -- 生成新列名
23 SELECT @Columns = @Columns + ', [' + ColName + '_' + CAST(ROW_NUMBER() OVER (PARTITION BY ColName ORDER BY rowNum) AS NVARCHAR(10)) + ']'
24 FROM (
25     SELECT
26         rowNum,
27         ColName
28     FROM (
29         SELECT *
30         FROM #SalesByQuarter
31     ) s
32     UNPIVOT (
33         Value FOR ColName IN (Q1, Q2, Q3, Q4)
34     ) AS unpvt
35 ) t;
36 
37 SET @Columns = STUFF(@Columns, 1, 2, '');
38 
39 -- 生成动态 SQL
40 SET @DynamicSQL = '
41 SELECT
42     ' + @Columns + '
43 FROM (
44     SELECT
45         CONCAT(ColName, ''_'', ROW_NUMBER() OVER (PARTITION BY ColName ORDER BY rowNum)) AS NewColName,
46         Value
47     FROM (
48         SELECT *
49         FROM #SalesByQuarter
50     ) s
51     UNPIVOT (
52         Value FOR ColName IN (Q1, Q2, Q3, Q4)
53     ) AS unpvt
54 ) AS SourceTable
55 PIVOT (
56     MAX(Value)
57     FOR NewColName IN (' + @Columns + ')
58 ) AS PivotTable;';
59 
60 -- 执行动态 SQL
61 EXEC sp_executesql @DynamicSQL;
62 
63 -- 删除示例表
64 DROP TABLE #SalesByQuarter;

 

posted @ 2025-03-26 15:30  常威打来福  阅读(22)  评论(0)    收藏  举报