将可变数量的行旋转到列

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

我目前正在尝试将某些行 PIVOT 到列.问题是我并不总是知道有多少行可用.我们来看一个例子:

I'm currently attempting to PIVOT some rows to columns. The problem is that I don't always know how many rows will be available. Let's look at an example:

Values_Table                         Columns_Table
------------                         -----------
ID                                   ID
ColumnsTableID                       GroupID
Value                                ColumnName

结果"

Columns_Table
---------------
ID   |   GroupID   |   ColumnName
---------------------------------     
0        1             Cats
1        1             Dogs
2        1             Birds
3        2             Pontiac
4        2             Ford
5        3             Trex
6        3             Raptor
7        3             Triceratops
8        3             Kentrosaurus

静态数据透视的 SQL FIDDLE 示例.我正在尝试实现动态支点 - http://sqlfiddle.com/#!3/2be82/1

SQL FIDDLE EXAMPLE of a STATIC pivot. I am trying to achieve a dynamic pivot - http://sqlfiddle.com/#!3/2be82/1

所以，这是我的两难境地:在这种情况下，我希望能够基于 GroupID 旋转未知数量的列.

So, here is my dilemma: I want to be able to pivot an unknown number of columns based on, in this scenario, the GroupID.

我希望能够 PIVOT，例如，将 GroupID 3 中的所有行转换为列.我需要在不知道 groupID 3 中有多少行的情况下执行此操作.

I want to be able to PIVOT, for example, all the rows in GroupID 3 into columns. I would need to do this without knowing how many rows are in groupID 3.

数据库的设计是一成不变的，所以我对此无能为力.我所能做的就是利用我所拥有的:(

The design of the database is set in stone, so I can't do anything about that. All I can do is work with what I have :(

那么，这就是说 - 是否有人对如何完成这项任务有任何建议，即根据本示例中的 groupID 将未知数量的行 PIVOTing 到列中?

So, that said- does anyone have any suggestions on how to accomplish this task of PIVOTing an unknown number of rows into columns based on, in this example, the groupID?

推荐答案

如果你不打算提前知道值，那么你需要看看使用 动态 SQL.这将创建一个将被执行的 SQL 字符串，这是必需的，因为在运行查询时必须知道列的列表.

If you are not going to know the values ahead of time, then you will need to look at using dynamic SQL. This will create a SQL String that will be executed, this is required because the list of columns must be known when the query is run.

代码将类似于:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX),
    @groupid as int

set @groupid = 3

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(GroupName) 
                    from Columns_Table
                    where groupid = @groupid
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT ' + @cols + ' 
            from 
            (
              SELECT B.GroupName, A.Value
                , row_number() over(partition by a.ColumnsTableID
                                    order by a.Value) seq
              FROM Values_Table AS A 
              INNER JOIN Columns_Table AS B 
                ON A.ColumnsTableID = B.ID
              where b.groupid = '+cast(@groupid as varchar(10))+' 
            ) p
            pivot 
            (
                min(P.Value)
                for P.GroupName in (' + @cols + ')
            ) p '

execute sp_executesql @query;

参见 SQL Fiddle with Demo.如果 groupid 为 3，则结果为:

See SQL Fiddle with Demo. For groupid of 3, then the result will be:

|   KENTROSAURUS |     RAPTOR |     TREX |    TRICERATOPS |
| whatisthiseven | Itsaraptor | Jurassic | landbeforetime |
|         (null) |       zomg |     Park |         (null) |

相关文章