Nandy Nandy - 1 month ago 6
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

Try This.....

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

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

            pivot
            (
              max(Hobbies)
              for Hobyno in ([Hobby1],[Hobby2],[Hobby3] )
            ) piv;
Comments