将可变行数转换为列 [英] PIVOTing variable number of rows to columns

问题描述

我目前正在尝试将某些行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

STATIC枢轴的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.

例如，我希望能够将GroupID 3中的所有行都列为PIVOT.我需要在不知道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 :(

那么，说-在这个例子中，有没有人对如何完成将PIVOT将未知数量的行分成列的工作有任何建议?

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?

推荐答案

如果您不希望提前知道这些值，则需要使用

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提琴.对于3的组ID，结果将是:

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

这篇关于将可变行数转换为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！