在不使用GROUPING BY的情况下在SQL Server中旋转或转置表 [英] Pivot or transpose a table in SQL Server without GROUPING BY

问题描述

我需要在SQL Server中旋转具有以下结构的表:

I need to pivot a table in SQL Server with the following structure:

CREATE TABLE table1 (
    ColumnNumber int,
    RowNumber int,
    CellData nvarchar(50)
)

INSERT INTO table1 VALUES
(1, 1, 'Orange'),
(2, 1, 'Apple'),
(3, 1, 'Banana'),
(1, 2, 'Grape'),
(2, 2, 'Corn'),
(3, 2, 'Lemon'),
(1, 3, 'Tomato'),
(2, 3, 'Lettuce'),
(3, 3, 'Onion')

我需要生成的表看起来像这样:

And I need the resulting table to look like this:

因此，ColumnNumber行中的单元格现在是结果表的列名.最难的部分是不同列号的数量是可变的(所以现在，我们有3个列号，但是明天可能有6或10).

So the cells in ColumnNumber row are now the Column Names of the resulting table. The hardest part is that the amount of different column numbers is variable (so now, we have 3 column numbers, but tomorrow there could be 6 or 10).

我一直在研究PIVOT函数，但是所有示例都包含一个GROUP BY，而且，正如您在此处看到的那样，我需要更像转置" excel函数的东西.

I've been looking at the PIVOT function, but all the examples include a GROUP BY, and, as you can see here, I need something more like a "transpose" excel function.

谢谢!!

推荐答案

可以使用

This can be accomplished using the PIVOT function. The GROUP BY will work because you have an indicator that makes each of the rows distinct. For your data the indicator is the rowNumber column.

如果您有固定数量的列，那么您将希望使用静态枢轴对它们进行硬编码.该代码将类似于以下内容:

If you have a set number of columns, then you will want to hard-code them using a static pivot. The code will be similar to this following:

select [1], [2], [3]
from
(
  select colNumber, RowNumber, CellData
  from yourtable
) src
pivot
(
  max(CellData)
  for colnumber in ([1], [2], [3])
) piv;

请参见带有演示的SQL小提琴.

在您的情况下，您声明将拥有未知数量的列.如果您的情况如此，那么您将需要使用动态sql来构建要透视的列列表.我演示了静态版本，因为它使将代码转换为动态SQL更容易.

In your case, you stated that you will have a unknown number of columns. If that is your situation then you will need to use dynamic sql to build the list of columns to pivot. I demonstrated the static version because it makes it easier to convert the code to dynamic SQL.

动态sql版本的关键是获取列列表，这是通过查询表并创建列名称字符串来完成的.这是通过FOR XML PATH完成的:

The key to the dynamic sql version is getting the list of columns which is done by querying your table and creating a string of the column names. This is done using FOR XML PATH:

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(colNumber) 
                    from yourtable
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

然后将此列表添加到您生成的查询字符串中，然后最终代码为:

This list is then added into the query string that you generate and the final code is then:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(colNumber) 
                    from yourtable
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT ' + @cols + ' 
             from 
             (
                select colNumber, rowNumber, CellData
                from yourtable
            ) x
            pivot 
            (
                min(CellData)
                for colNumber in (' + @cols + ')
            ) p '

execute(@query)

请参见带有演示的SQL小提琴.

两者都给出结果:

|      1 |       2 |      3 |
-----------------------------
| Orange |   Apple | Banana |
|  Grape |    Corn |  Lemon |
| Tomato | Lettuce |  Onion |

这篇关于在不使用GROUPING BY的情况下在SQL Server中旋转或转置表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！