Dynamically column names from select sql Dynamically column names from select sql sql sql

Dynamically column names from select sql


Is this what you were looking for:

DECLARE @cols AS NVARCHAR(MAX),@query  AS NVARCHAR(MAX)select @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(ColumnName)     from tempData    group by ColumnName, nameFOR XML PATH(''), Type).value('.', 'NVARCHAR(MAX)') ,1,1,'')set @query = N'SELECT Name, ' + @cols + N' from  (    select Name, value, ColumnName    from tempData) xpivot (    SUM(value)    for ColumnName in (' + @cols + N')) p 'exec sp_executesql @query;

Changing this in your fiddle return the rows as you need it.


Try it like this:

DECLARE @cols AS NVARCHAR(MAX)=STUFF((    SELECT DISTINCT ',[' + ColumnName + ']'    FROM tempData    FOR XML PATH('')),1,1,'');DECLARE @SqlCmd VARCHAR(MAX)='SELECT p.* FROM (    SELECT *    FROM tempData ) AS tbl PIVOT (    SUM(Value) FOR ColumnName IN(' +  @cols +') ) AS p'; EXEC(@SqlCmd);