Arnun Sae-Lim Arnun Sae-Lim - 11 months ago 43
SQL Question

Can I pivot dynamic table with no group by in SQL Server

This is my data in table (left join from field table and value table)

enter image description here

This is my expected result table after use pivot function

enter image description here

Thanks for help ^___^

Answer Source

I suggest you to use dynamic SQL as fieldnames number may very in future:

DECLARE @columns nvarchar(max),
        @sql nvarchar(max)

SELECT @columns = COALESCE(@columns,'') + ',' + QUOTENAME(c.fieldname)
FROM [Columns] c
ORDER BY c.cid

SELECT @sql = N'
    SELECT  v.[row],
    FROM [Values] v
    INNER JOIN [Columns] c
        ON v.cid = c.cid
    ) t
    MAX(fieldvalue) FOR fieldname IN ('+STUFF(@columns,1,1,'')+')
) pvt'

EXEC sp_executesql @sql

Will output:

row FirstName   LastName    Email               Phone
1   Arnun       Saelim 0922743838
2   Micheal     Saelim   0886195353