枢轴SQL语句

Question

我有一个表像这样的（当然还有更多的值，但你的想法）：

ID  Name 
---  ---- 
1  A 
1  B 
2  C 
3  D 
4  A 
4  D 
4  E 
4  F 
4  G 
4  H 

我想写给出一个查询，将输出这是一个ID不能有超过6个名字。

ID  Name1  Name2  Name3  Name4  Name5 Name6 
--- ------  ------  ------ ------ ------ ----- 
1  A    B 
2  C 
3  D 
4  A    D   E   F   G  H 

Answer 1

尝试：

WITH rows AS (
    SELECT t.id, 
      t.name, 
      ROW_NUMBER() OVER (PARTITION BY t.id ORDER BY t.name) 'rank' 
    FROM TABLE t) 
    SELECT r.id, 
     MAX(CASE WHEN r.rank = 1 THEN r.name ELSE NULL END) AS Name1, 
     MAX(CASE WHEN r.rank = 2 THEN r.name ELSE NULL END) AS Name2, 
     MAX(CASE WHEN r.rank = 3 THEN r.name ELSE NULL END) AS Name3, 
     MAX(CASE WHEN r.rank = 4 THEN r.name ELSE NULL END) AS Name4, 
     MAX(CASE WHEN r.rank = 5 THEN r.name ELSE NULL END) AS Name5, 
     MAX(CASE WHEN r.rank = 6 THEN r.name ELSE NULL END) AS Name6, 
    FROM rows r 
GROUP BY r.id 

非CTE相当于：

SELECT r.id, 
     MAX(CASE WHEN r.rank = 1 THEN r.name ELSE NULL END) AS Name1, 
     MAX(CASE WHEN r.rank = 2 THEN r.name ELSE NULL END) AS Name2, 
     MAX(CASE WHEN r.rank = 3 THEN r.name ELSE NULL END) AS Name3, 
     MAX(CASE WHEN r.rank = 4 THEN r.name ELSE NULL END) AS Name4, 
     MAX(CASE WHEN r.rank = 5 THEN r.name ELSE NULL END) AS Name5, 
     MAX(CASE WHEN r.rank = 6 THEN r.name ELSE NULL END) AS Name6, 
    FROM (SELECT t.id, 
       t.name, 
       ROW_NUMBER() OVER (PARTITION BY t.id ORDER BY t.name) 'rank' 
      FROM TABLE t) r 
GROUP BY r.id 

参考：

• ROW_NUMBER()

Answer 2

我创建了一个名为pivot_query的存储过程，使得PIVOT语句更加灵活一些。它的来源是here。还有一个关于如何使用它的example。

借用一块从下面OMG小马代码，并更改查询了一下， 然后调用pivot_query应该是这样的：

declare @mySQL varchar(MAX) 

set @mySQL = ' 
SELECT 
    t.id, 
    t.name, 
    ''Name'' + cast(ROW_NUMBER() OVER (PARTITION BY t.id ORDER BY t.name) as varchar(2)) rank 
FROM 
    TestData t' 

exec pivot_query @mySQL, 'Id', 'rank', 'max(Name)' 

和现在的结果是这样的：

Id   Name1 Name2 Name3 Name4 Name5 Name6 
---------- ----- ----- ----- ----- ----- ----- 
1   A  B  NULL NULL NULL NULL 
2   C  NULL NULL NULL NULL NULL 
3   D  NULL NULL NULL NULL NULL 
4   A  D  E  F  G  H  

不完全确定你要展示什么，但。 :-)

这不会本质上限制输出到6个列，虽然，它会继续往上走，除非你添加一个where子句来明确排除上述行列6.