pivot table so users and their dates go to one row

Question

I want to go from something like this:

username    CourseID    DateTaken
mikeb       LST          5/9/2014
mikeb       SELAGENT001  4/4/2013
chrisp      AGT001       2/21/2012
chrisp      SELAGENT001  6/27/2011

to something like this:

Username    LST         SELAGENT001 AGT001
mikeb       5/9/2014    4/4/2013    
chrisp                  6/27/2011   2/21/2012

I tried missing around with crosstab, but since I'm dealing with dates I'm having a hard time. Any help?

score 0 · Answer 1 · edited May 23 '17 at 12:23

Here's ANSI SQL solution to this, assuming you only have one row per username, courseid pair it would return expected results. Otherwise, it'd take the maximum datetaken value for each pair.

SELECT
  username,
  MAX(CASE WHEN courseid = 'LST' THEN datetaken END) AS lst,
  MAX(CASE WHEN courseid = 'SELAGENT001' THEN datetaken END) AS selagent001,
  MAX(CASE WHEN courseid = 'AGT001' THEN datetaken END) AS agt001
FROM t
GROUP BY username

If you want Postgres specific answer there is a crosstab() function in tablefunc module, but I personally always favor portable solutions over DBMS specific.

For reference you could also check this answer

pivot table so users and their dates go to one row

1 Answers1