代码之家 › 专栏 › 技术社区 › LeWoody

透视SQL语句

pivot sql-server-2008 tsql sql-server sql

LeWoody · 技术社区 · 15 年前

我有一张这样的表(当然还有很多值,但是你知道了):

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

2 回复 | 直到 13 年前

OMG Ponies 15 年前

尝试:

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()

Tim Cooper 13 年前

我创建了一个名为pivot\u query的存储过程,使pivot语句更加灵活。它的来源是 here . 还有一个 example 如何使用它。

从下面的omg ponies中借用一段代码,并稍微更改查询, 然后对pivot查询的调用如下所示:

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个以上的列,否则它将继续上升。