CODEWITHSUNDEEP
sql-serversql-server-2008
Jeffrey kalampukattussery
Jeffrey kalampukattussery

Reputation: 25

Convert rows into columns using pivot

This is my table:

CaseID                                AttributeName  AttributeValue
03E07546-9C10-4399-B840-04F9CE211FB8  Case Title     deepa04
03E07546-9C10-4399-B840-04F9CE211FB8  Body Part      hand
03E07546-9C10-4399-B840-04F9CE211FB8  Diagnosis      123
E999866E-E8BE-4442-8A87-C419D482022E  Case Title     deep_case
E999866E-E8BE-4442-8A87-C419D482022E  Body Part      leg
E999866E-E8BE-4442-8A87-C419D482022E  Diagnosis      123

I need to convert this into:

CaseID                                Case Title  Body Part  Diagnosis
03E07546-9C10-4399-B840-04F9CE211FB8  deepa04     hand       123
E999866E-E8BE-4442-8A87-C419D482022E  deep_case   leg        123

How can I achieve this using dynamic pivot functions in SQL Server 2008?

Upvotes: 0

Views: 1213

Answers (2)

Joe G Joseph
Joe G Joseph

Reputation: 24046

try tihis:

select CaseID  ,   [Case Title],[Body Part],  Diagnosis
from <your_table>
PIVOT (MAX(AttributeValue) FOR AttributeName IN 
([Case Title],[Body Part],  Diagnosis)) P

Edit1: If column Names are Dynamic You could do this

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(AttributeName) 
                    from <your_table>
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')



set @query = 'SELECT CaseID  , ' + @cols + ' from t_case
             pivot 
            (
                MAX(AttributeValue)
                for AttributeName in (' + @cols + ')
            ) p '
print(@query)
execute(@query)

Upvotes: 1

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

This is how you can use pivot.

select P.CaseID, P.[Case Title], P.[Body Part], P.Diagnosis
from (
     select CaseID, AttributeName, AttributeValue
     from YourTable
     ) as T
pivot (
      min(AttributeValue) 
      for AttributeName in ([Case Title], [Body Part], [Diagnosis])
      ) as P

Upvotes: 0

Related Questions