Nandy Nandy - 1 year ago 72
SQL Question

Pivot columns with multiple values

Table #Temp:

ID Name Hobbies
1 xxx Reading
1 xxx Sports
1 xxx Dance
2 xyz Dance
3 yyy Swimming
3 yyy Reading

Now, I want the result set to be:

ID Name Hobby1 Hobby2 Hobby3
1 xxx Reading Sports Dance
2 xyz Cooking
3 yyy Coloring Swimming

How to accomplish this? I have used multiple values of hobbies so I can't hard-code.

Answer Source

Try This.....

            select ID  , Name  ,  Hobby1    , Hobby2  ,  Hobby3 
            ID , Name,Hobbies, 
                case when Hobyno ='1' then 'Hobby1' 
                when Hobyno ='2' then 'Hobby2' 
                when Hobyno ='3' then 'Hobby3' 
                else null end as HobyNo 

             select   *,ROW_NUMBER() OVER ( PARTITION  BY id order by id ) HobyNo
              from   #temp
            where a.HobyNo <4  -- Add if you want more than 3 hobbie also edit case when

              for Hobyno in ([Hobby1],[Hobby2],[Hobby3] )
            ) piv;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download