透视 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 语句更加灵活。它的来源是此处。还有一个如何使用它的示例。

借用下面 OMG Ponies 的一段代码，并稍微更改一下查询，
那么对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 以上的排名。