sql query to convert column to row

given the table below
ID    Event        Time  
1      M               05:00:00.0000000
1      T               06:00:00.0000000
1      A               07:00:00.0000000
2      M               01:00:00.0000000
2      A               02:00:00.0000000
3      T               04:00:00.0000000

what is the sql query that gives me
ID    A             M               T
1     7              5                6
2     2              1
3                                       4

 how about if Event column takes more than 3 values (i.e. 30)? can the query be generic and does not rely on the Event values?      
hamid441Asked:
Who is Participating?
 
lluddenCommented:
You can use a dynamic pivot table if you have an unknown number of events

DECLARE @PivotColumnHeaders varchar(MAX)
SELECT @PivotColumnHeaders =
  COALESCE(
    @PivotColumnHeaders + ',[' + UC.Event + ']',
    '[' + Event + ']'
  )
FROM (SELECT Event FROM #T1 GROUP BY Event) UC

DECLARE @PQuery varchar(MAX) = '
SELECT * FROM (SELECT ID, Event, datepart(hh,[Time]) AS [Time] FROM #T1  T0) T1
PIVOT (SUM([Time]) FOR Event IN (' + @PivotColumnHeaders + ') ) AS P'
EXECUTE (@PQuery)
0
 
TempDBACommented:
you can use pivot for this.
0
 
TempDBACommented:
0
 
skullnobrainsCommented:
assuming you can enumerate the possible contents of event columns, many ways available looking like this one

select Id, sum(if(event='A',time,0)) as A, sum(if(event='B',time,0)) as B,  ... from table group by Id
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.