0

Sorry for the uninformative title, I do not know how to name this operation. Here's an example that I hope will help.

We have users, for which we log events. Events can be of different types. We want to generate a report (a new table) that will say if an event as occured for an user; for example:

Events             x  EventInfo            =  UserEvent     
---                   ---                     ---
UserId  | EventId     EventId | Type          UserId | login | verify
------------------    -----------------       ------------------------           
1       | 1           1       | login         1      | True  | True
1       | 2           2       | verify        2      | False | False
2       | 3           3       | login

Additional constraints:

  • We do not know the event types beforehand (neither their name or the number of distinct types)
  • Has to happen entirely in the database
Winks
  • 395
  • 2
  • 9
  • because you don't know the number or names of event types ahead of time that you want as columns you will have to use dynamic sql to discover those details and create a select statement using conditional aggregation like @sgeddes ' answer. – Matt Aug 07 '16 at 16:36

1 Answers1

1

What are you attempting to do is a form of a table pivot. Here's one option using conditional aggregation:

select e.userid, 
   max(case when ei.type = 'login' then 'True' else 'False' end) as login,
   max(case when ei.type = 'verify' then 'True' else 'False' end) as verify
from events e 
   join eventinfo ei on e.eventid = ei.eventid
group by e.userid

You may need an outer join if you have records in your event table that don't exist in the eventinfo table.

If you don't know the type, you'll have to use dynamic sql, There are a lot of examples out there:

Community
  • 1
  • 1
sgeddes
  • 62,311
  • 6
  • 61
  • 83
  • This probably works for the simple example, but fails in the case where we do not know the types of events at the time of writing the query. Is it possible to generalize your solution? – Winks Aug 07 '16 at 16:34
  • @Winks -- just noticed your edits and concerns. You need to use `dynamic sql` to create the `pivot`. Look at that answer by bluefeet, perfect example using `if` instead of `case`, but works the same. – sgeddes Aug 07 '16 at 16:36