SQL Server 2008行转列求助:按Rank将行数据转置为列格式
解决SQL Server中按Rank转置多实例数据的问题
嘿,我明白你折腾这个SQL转置好久没搞定了,别着急,咱们一步步来解决它!首先我先把你提到的表结构补全(根据描述推断),然后给出两种实用的解决方案——分别对应Rank数量固定和动态的场景。
先明确表结构
原表(假设名为SourceTable)
| ID | USER_ID | FINDING | START DATE | END DATE | RANK |
|---|---|---|---|---|---|
| 1 | 101 | A | 2023-01-01 | 2023-01-10 | 1 |
| 2 | 101 | B | 2023-01-11 | 2023-01-20 | 2 |
| 3 | 102 | C | 2023-02-01 | 2023-02-10 | 1 |
目标表(转置后)
每个USER_ID占一行,不同Rank的FINDING、起始/结束日期转成带Rank后缀的列:
| USER_ID | FINDING_1 | START_DATE_1 | END_DATE_1 | FINDING_2 | START_DATE_2 | END_DATE_2 |
|---|---|---|---|---|---|---|
| 101 | A | 2023-01-01 | 2023-01-10 | B | 2023-01-11 | 2023-01-20 |
| 102 | C | 2023-02-01 | 2023-02-10 | NULL | NULL | NULL |
方案1:Rank数量固定时用条件聚合
如果你的数据中Rank的最大值是确定的(比如最多3个),直接用条件聚合最直观,不需要复杂的语法:
SELECT USER_ID, -- 处理Rank=1的字段 MAX(CASE WHEN RANK = 1 THEN FINDING END) AS FINDING_1, MAX(CASE WHEN RANK = 1 THEN [START DATE] END) AS START_DATE_1, MAX(CASE WHEN RANK = 1 THEN [END DATE] END) AS END_DATE_1, -- 处理Rank=2的字段 MAX(CASE WHEN RANK = 2 THEN FINDING END) AS FINDING_2, MAX(CASE WHEN RANK = 2 THEN [START DATE] END) AS START_DATE_2, MAX(CASE WHEN RANK = 2 THEN [END DATE] END) AS END_DATE_2, -- 可以继续添加更多Rank对应的列,比如Rank=3、4... FROM SourceTable GROUP BY USER_ID;
说明:
- 用
CASE WHEN筛选对应Rank的行,MAX函数用来聚合(因为每个USER_ID+Rank只会有一行,MAX不会改变值,只是用来消除分组后的NULL) - 注意列名有空格(
START DATE、END DATE),必须用方括号[]包裹才能被SQL Server识别
方案2:Rank数量动态时用动态SQL
如果你的数据中Rank的数量不确定(可能随时新增更高的Rank值),用动态SQL自动生成所有需要的列,不用手动修改代码:
DECLARE @cols NVARCHAR(MAX); DECLARE @query NVARCHAR(MAX); -- 第一步:自动生成所有Rank对应的列语句 SELECT @cols = STRING_AGG( CONCAT( 'MAX(CASE WHEN RANK = ', RANK, ' THEN FINDING END) AS FINDING_', RANK, ',', 'MAX(CASE WHEN RANK = ', RANK, ' THEN [START DATE] END) AS START_DATE_', RANK, ',', 'MAX(CASE WHEN RANK = ', RANK, ' THEN [END DATE] END) AS END_DATE_', RANK ), ',' ) FROM (SELECT DISTINCT RANK FROM SourceTable) AS RankValues; -- 第二步:构建完整的查询语句 SET @query = CONCAT(' SELECT USER_ID, ', @cols, ' FROM SourceTable GROUP BY USER_ID; '); -- 第三步:执行动态生成的SQL EXEC sp_executesql @query;
说明:
STRING_AGG函数(SQL Server 2017及以上支持)用来把所有Rank对应的列语句拼接成一个字符串- 如果你的SQL Server版本低于2017,可以用
FOR XML PATH来代替STRING_AGG实现字符串拼接 - 动态SQL会自动识别原表中所有不同的Rank值,生成对应的转置列,无需手动维护
内容的提问来源于stack exchange,提问作者user3263892
相关产品推荐
相关产品推荐

