SQL Server Dynamically Pivot on column with +100 different values

Question

Here is a table that I have in SQL Server and here are the first three rows. I need to pivot on the node name column.

Here is the table I have

CREATE TABLE [dbo].[VanHalen99](
    [FileName] [varchar](100) NULL,
    [NodeName] [varchar](max) NULL,
    [NodeValue] [varchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO


FileName                         NodeName                         NodeValue
-------------------------------- ------------------------------   --------------------------
202111249349301206_public.xml    FormerOfcrEmployeesListedInd     false
202140789349300129_public.xml    TitleTxt                         Board Member
202141379349301969_public.xml    PersonTitleTxt                   SECRETARY/TRASURER

I need to "rotate" the table so that the output looks like this:

FileName                         FormerOfcrEmployeesListedInd     TitleTxt                      PersonTitleTxt
-------------------------------- ------------------------------   --------------------------    -------------------
202111249349301206_public.xml         false
202140789349300129_public.xml                                      Board Member
202141379349301969_public.xml                                                                    SECRETARY/TRASURER

There are more than 100 possible columns which can often change. I suspect the pivot operator would be used but I'm struggling to get it to work.

Thank you in advance!!!

 I tried this but it gave me an error 
DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

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

set @query = 'SELECT date, ' + @cols + ' from 
            (
                select FileName
                    , NodeValue
                    , NodeName
                from VanHalen99
           ) x
            pivot 
            (
                 nodeValue
                for NodeName in (' + @cols + ')
            ) p '


execute(@query)

This type of question comes up often, have you tried anything yet? — Aaron Bertrand, Mar 07 '22 at 21:30
In the @cols I see QUOTENAME(c.category) shouldn't that be NodeName ? Also, in the final query, date should be FileName — John Cappelletti, Mar 07 '22 at 22:09
Here is a working sample https://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=a878b48b2f54ceedcd480846b4a5bcf5 — John Cappelletti, Mar 07 '22 at 22:17
In the sample provided, also note that we need an aggregate in the PIVOT ... max(NodeValue) — John Cappelletti, Mar 07 '22 at 22:20

SQL Server Dynamically Pivot on column with +100 different values

0 Answers0