Arnun Sae-Lim Arnun Sae-Lim - 3 months ago 9
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

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 *
FROM (
    SELECT  v.[row],
            c.fieldname,
            v.fieldvalue
    FROM [Values] v
    INNER JOIN [Columns] c
        ON v.cid = c.cid
    ) t
PIVOT (
    MAX(fieldvalue) FOR fieldname IN ('+STUFF(@columns,1,1,'')+')
) pvt'

EXEC sp_executesql @sql

Will output:

row FirstName   LastName    Email               Phone
1   Arnun       Saelim      Arnun.s@outlook.com 0922743838
2   Micheal     Saelim      Micheal@gmail.com   0886195353