sql - PIVOTing variable number of rows to columns

Question

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

RESULTS"

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 EXAMPLE of a STATIC pivot. I am trying to achieve a dynamic pivot - http://sqlfiddle.com/#!3/2be82/1

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

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

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?

Answer 1

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.

The code will be similar to:

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;

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