CODEWITHSUNDEEP
sqlsql-serverpivot
Arnun Sae-Lim
Arnun Sae-Lim

Reputation: 69

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 ^___^

Upvotes: 0

Views: 56

Answers (2)

gofr1
gofr1

Reputation: 15987

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      [email protected] 0922743838
2   Micheal     Saelim      [email protected]   0886195353

Upvotes: 1

PacoDePaco
PacoDePaco

Reputation: 699

I'm not sure why group by got into your mind, but here is a working example of what you are trying to achieve.

    select *  into #temp from   
( values
(1,'A','AV'),
(1,'B','BV'),
(1,'C','CV'),
(2,'A','AV'),
(2,'B','BV'),
(2,'C','CV')) 
as t(row, FieldName, FieldValue)

select * 
from #temp
PIVOT
(MAX(FieldValue) FOR FieldName in ([A],[B],[C])) as pvt

Does that work for you?

Upvotes: 1

Related Questions