行列转换——SQL

### SQL 中行列转换的统计方法与实现方式 在 SQL 数据库操作中，`PIVOT` 和 `UNPIVOT` 是两种常用的工具，用于实现数据的行列互换。以下是关于这两种功能的具体介绍以及如何通过它们来完成统计数据的操作。 #### 使用 PIVOT 进行行列转换并统计 `PIVOT` 将数据库中的行数据转化为列数据，通常会涉及某种形式的数据聚合。其基本语法如下： ```sql SELECT * FROM ( SELECT <columns> FROM <table> ) AS SourceTable PIVOT ( <aggregate_function>(<column_to_aggregate>) FOR <column_to_pivot> IN (<pivoted_columns>) ) AS PivotTable; ``` 此结构允许用户指定哪些列应被旋转为新列名称，并定义这些列上的聚合逻辑[^1]。例如，在销售记录表中按产品类别汇总销售额时可以这样写： ```sql SELECT Category, SUM(SalesAmount) AS TotalSales FROM SalesRecords GROUP BY Category; -- 或者使用 PIVOT 来展示不同年份下的总销量对比： SELECT Year, COALESCE([Electronics], 0) AS Electronics_Sales, COALESCE([Clothing], 0) AS Clothing_Sales FROM ( SELECT SaleYear AS Year, ProductCategory, SalesAmount FROM SalesData ) AS SourceTable PIVOT ( SUM(SalesAmount) FOR ProductCategory IN ([Electronics], [Clothing]) ) AS PivotTable; ``` 这里利用了 `SUM()` 函数作为聚合手段，并且指定了两个可能的产品分类——电子产品和服装类商品分别对应的新列名[^2]。 #### UNPIVOT 的应用场合及其优势 相对地，当需要将宽表格变窄长型时，则需要用到 `UNPIVOT` 命令。它能够把多个字段压缩成单一的一组键值对表示法。下面是一个例子说明如何从固定格式的成绩单提取科目成绩信息： 原始数据表 Grades 如下所示： | StudentID | MathScore | EnglishScore | |-----------|-----------|---------------| | S001 | 85 | 76 | 执行以下查询后得到的结果将是每门课单独一行的形式呈现出来： ```sql SELECT StudentID, SubjectName, ScoreValue FROM Grades UNPIVOT( ScoreValue FOR SubjectName IN (MathScore, EnglishScore) ) AS UnPvtGrades; ``` 最终输出将会像这样子排列开来以便进一步分析处理[^3]: | StudentID | SubjectName | ScoreValue | |-----------|-------------|------------| | S001 | MathScore | 85 | | S001 | EnglishScore| 76 | 这种变换特别适合于那些希望统一管理多种属性指标的应用场景之中。 #### 动态生成 PIVOT 查询语句 有时候预先不知道所有要参与运算的维度项列表长度或者具体内容是什么样的情况之下，就需要构建动态SQL字符串再加以解析运行的方式达成目标。比如根据时间跨度内的特定参数集合计算平均值之类的任务就可以采用这种方法解决: 假设我们有一张存储传感器读数的历史记录表 Speetab_1_12 ，其中包含了若干速度测量值（spee1 至 spee6），现在想获取某一天之内各个时刻点针对这六个方向的速度均值得话，那么可以通过编写类似这样的脚本来自动化这一过程： ```sql DECLARE @cols NVARCHAR(MAX),@query NVARCHAR(MAX); SET @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(c.name) FROM sys.columns c INNER JOIN sys.tables t ON c.object_id=t.object_id AND t.name='Speetab_1_12' WHERE c.name LIKE 'spee%' ORDER BY column_id ASC FOR XML PATH('')),1,1,''); SET @query = N'SELECT data,time,'+@cols+' INTO #TempResult FROM YY2FSC.dbo.Speetab_1_12 unpivot(speeVal for speeName in ('+@cols+')) up GROUP BY data,time;'; EXEC sp_executesql @query; -- 后续可继续在此基础上做更多复杂运算... ``` 上述代码片段展示了怎样自动发现符合条件的所有列并将之纳入到后续步骤当中去[^4]。