获取列中的前 6 个项目以在 SQL 中旋转到一行

2022-01-22 00:00:00 pivot tsql sql-server

我无法在 SQL 中获取要旋转的列.我想将前 6 个结果从一列转换为一行.我正在旋转的列可以有少于或多于 6 个结果，但我想忽略前 6 名之外的任何内容.

I'm having trouble getting a column to pivot in SQL. I'd like to pivot the top 6 results from one column into a row. The column I'm pivoting can have less than or more than 6 results to start with but I want to ignore anything beyond the top 6.

我的 Table1 如下所示:

My Table1 looks like this:

ID | GroupID | CodeNum
----------------------
1  | 1       | 111
2  | 1       | 222
3  | 1       | 333
4  | 1       | 444
5  | 1       | 555
6  | 1       | 666
7  | 1       | 777
8  | 2       | 111
9  | 2       | 888
10 | 3       | 999

我希望我的输出看起来像这样:

And I want my output to look like this:

GroupID | Code1 | Code2 | Code3 | Code4 | Code5 | Code6
-------------------------------------------------------
1       | 111   | 222   | 333   | 444   | 555   | 666
2       | 111   | 888   |       |       |       |
3       | 999   |       |       |       |       |

我试过这段代码:

SELECT  GroupID
        , [Code1] = CASE WHEN rn = 1 THEN CodeNum END
        , [Code2] = CASE WHEN rn = 2 THEN CodeNum END
        , [Code3] = CASE WHEN rn = 3 THEN CodeNum END
        , [Code4] = CASE WHEN rn = 4 THEN CodeNum END
        , [Code5] = CASE WHEN rn = 5 THEN CodeNum END
        , [Code6] = CASE WHEN rn = 6 THEN CodeNum END
FROM    (
            SELECT  TOP 6 GroupID
                    , rn = ROW_NUMBER() OVER (ORDER BY ID)
                    , CodeNum
            FROM    Table1      
        ) q
GROUP BY
        GroupID

但我收到一条错误消息，提示 Column 'q.rn' 在选择列表中无效，因为它既不包含在聚合函数或 GROUP BY 子句中.

But I'm getting an error that says Column 'q.rn' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

当 CodeNum 每个 GroupID 可以有 1 到 12 个值时，有没有办法做到这一点?

Is there a way to do this when CodeNum could have between 1 and 12 values per GroupID?

推荐答案

由于您使用的是 GROUP BY，因此您需要按 SELECT 中的所有项目进行分组或使用聚合函数.

Since you are using a GROUP BY, you will need to group by all items in the SELECT or use an aggregate function.

您将需要使用以下内容来获取结果，然后将行 pivot 到列中:

You will want to use the following which will get the result and then pivot the rows into columns:

SELECT  GroupID
        , [Code1] = max(CASE WHEN rn = 1 THEN CodeNum END)
        , [Code2] = max(CASE WHEN rn = 2 THEN CodeNum END)
        , [Code3] = max(CASE WHEN rn = 3 THEN CodeNum END)
        , [Code4] = max(CASE WHEN rn = 4 THEN CodeNum END)
        , [Code5] = max(CASE WHEN rn = 5 THEN CodeNum END)
        , [Code6] = max(CASE WHEN rn = 6 THEN CodeNum END)
FROM    
(
  SELECT  GroupID
   , rn = ROW_NUMBER() OVER (PARTITION BY GroupID ORDER BY ID)
   , CodeNum
  FROM    Table1      
) q
GROUP BY GroupID

参见 SQL Fiddle with Demo

您会注意到我删除了 TOP 6 并添加了一个 GroupId 分区.如果您使用 TOP，那么您将只返回第一个 ID 的值.分区将返回每个 ID 的行，但外部选择仅返回前 6 行.

You will notice that I removed the TOP 6 and added a partition by GroupId. If you use the TOP, then you will only return the values for the first ID. The partition will return the rows for each ID, but the outer select is only returning the first 6 rows.

这给出了结果:

| GROUPID | CODE1 |  CODE2 |  CODE3 |  CODE4 |  CODE5 |  CODE6 |
----------------------------------------------------------------
|       1 |   111 |    222 |    333 |    444 |    555 |    666 |
|       2 |   111 |    888 | (null) | (null) | (null) | (null) |
|       3 |   999 | (null) | (null) | (null) | (null) | (null) |

注意，根据您的 SQL Server 版本，这也可以使用 PIVOT 函数编写:

Note, depending on your version of SQL Server, this could also be written using the PIVOT function:

select GroupID,
  [1] as Code1, 
  [2] as Code2, 
  [3] as Code3, 
  [4] as Code4, 
  [5] as Code5, 
  [6] as Code6
from
(
  SELECT  GroupID
   , rn = ROW_NUMBER() OVER (PARTITION BY GroupID ORDER BY ID)
   , CodeNum
  FROM Table1    
) d
pivot
(
  max(codenum)
  for rn in ([1], [2], [3], [4], [5], [6])
) piv;

参见 SQL Fiddle with Demo

相关文章