SQL Server - Dynamic Pivot Query

Question

I've been reading the various examples on this board but haven't been able to whip up a dynamic pivot query to fit my code. Any help would be appreciated!

I have two tables

AuxDef

Fieldnumber  Fieldlabel
------------------------
1            Buyer
2            Size Range
3            Source
4            Country
5            Vendor
ect...       ect...

AuxFields

PageID   FieldNumber   TextValue
-----------------------------------
1        1             Sam
1        2             S-L
1        3             Domestic
1        4             Canada
2        1             Kyla
2        3             Import
2        5             VendorName
2        6             Off-Shore
2        7             Fit 1
2        8             Yes
4        1             Sara
4        3             Import
4        4             China
ect..    ect..         ect..

What I would like to do is create a dynamic pivot that joins the two tables by fieldnumber and uses the fieldlabels as the column headers after pageid. It would look similar to this.

PageID   Buyer     Size Range   Source     Country   Vendor      Type       Status   Exclusive   ect..
------------------------------------------------------------------------------------------------
1        Sam       S-L          Domestic   Canada 
2        Kyla                   Import              VendorName  Off-Shore   Fit 1    Yes
4        Sara                   Import     China

I've tried examples on this site but I keep running into errors either when I replace the fields with my own or in the actual column generation.

Can you post the code you tried and what was the error it returned? — Tab Alleman, Aug 14 '15 at 16:41

score 0 · Accepted Answer · edited May 23 '17 at 11:51

You will need to create a dynamic pivot (credit to @bluefeet's answer here) something like this:

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

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.FieldLabel) 
            FROM auxdef c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT PageID, ' + @cols + ' from 
            (
             select 
                 PageId,
                 FieldLabel,
                 TextValue
            from AuxFields af
            inner join auxdef ad on af.FieldNumber = ad.FieldNumber
           ) x
            pivot 
            (
                 max(TextValue)
                for FieldLabel in (' + @cols + ')
            ) p '


execute(@query);

SQL Fiddle Demo

score 0 · Answer 2 · answered Aug 14 '15 at 18:08

Here's another way

sql fiddle

declare @sql nvarchar(max)
select @sql = 'select af.pageId'

select @sql = @sql + ', max(case when ad.fieldLabel = ''' + ad.fieldLabel + ''' then af.textValue end) as [' + ad.fieldLabel + ']' + char(13)
from auxDef ad

select @sql = @sql + 'from auxDef ad' + char(13)
+ 'inner join auxFields af on af.fieldNumber = ad.fieldNumber' + char(13)
+ 'group by af.pageId'

exec(@sql)

SQL Server - Dynamic Pivot Query

2 Answers2