Merging Multiple rows into one row by Entity Type

Question

I have a table like this which has an Entity type and Entity Item Id. I would like to group them by ID column and merge these records into one row into respective column type.

Id EntityItemId EntityTypeId
1   id1             1
1   id2             2
1   id3             3
1   id4             4
2   id5             1
2   id6             2
2   id7             3

Desired Output:

ID Entitytype1  Entitytype2  Entitytype3   Entitytype4
1   id1            id2           id3          id4
2   id5            id6           id7          null

Thanks

Answer 1

use pivot

    select ID ,[1] as Entitytype1,[2] as Entitytype2
   ,[3] as Entitytype3 ,[4] as Entitytype4 from 
    (
    select * from t
    ) src
    PIVOT
    ( 
    max(EntityItemId) for EntityTypeId in ([1],[2],[3],[4])
    )pv