Convert rows into columns using pivot

Question

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?

check this link weblogs.asp.net/salimfayad/archive/2008/01/30/… — StackTrace
– StackTrace, Commented Aug 8, 2012 at 6:24

Joe G Joseph · Accepted Answer · 2012-08-08 08:21:40Z

1

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)

edited Aug 8, 2012 at 8:21

answered Aug 8, 2012 at 6:33

Joe G Joseph

24.2k5 gold badges58 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Jeffrey kalampukattussery Over a year ago

but Case Title,Body Part,Diagnosis are dynamic values,in that case what we do?

Joe G Joseph Over a year ago

Then we can do a dynamic pivot query

Joe G Joseph Over a year ago

The column name we can store in a variable and the use it in the query

Joe G Joseph Over a year ago

@Jeffreykalampukattussery: I have updated my answer , Plz check it

Jeffrey kalampukattussery Over a year ago

thanks for that joe,but [Body Part],[Case Title],[Diagnosis],[DOB],[History],[Keywords],[Modality],[Sex] from t_case getting error "Invalid object name 't_case'." t_case is a temperory table,does dat making the error

|

Mikael Eriksson · Accepted Answer · 2012-08-08 06:31:58Z

0

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

answered Aug 8, 2012 at 6:31

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

1 Comment

Jeffrey kalampukattussery Over a year ago

but Case Title,Body Part,Diagnosis are dynamic values,in that case what we do?

Collectives™ on Stack Overflow

Convert rows into columns using pivot

2 Answers 2

8 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related