who visited the room

Question

I have a table with userid, roomid and timestamp fields.

Each room may have different userIds:

 userId1 roomid1 12:15:17
 userId1 roomid1 12:17:15
 userId2 roomid1 13:17:15 
 userId3 roomid2 12:15:15
 userId4 roomid2 12:15:18
 userId5 roomid2 12:15:25

And I need a query that will return something like this:

  roomid            1           2           3        4           5
  roomid1      userid1       userid2     userid3
  roomid2      userid4       userid5     userid6   userid7    userid8

so i could see what distinct users visited what distinct rooms during the whole game.

There may be up to 32 users in the room.

RDBMS matters here... but you're after a "dynamic pivot" ([SQL SERVER example](http://stackoverflow.com/questions/12210692/t-sql-dynamic-pivot) or 32 case statements with a max grouping. ([mySQL case example](http://stackoverflow.com/questions/20320631/how-to-pivot-a-table-in-mysql-using-case-statements)) — xQbert, Nov 11 '16 at 14:28
those things are possible in some RDBMS, but in my opinion, it's much better to do it on the client-side if you have one — Roman Pekar, Nov 11 '16 at 14:31

score 0 · Answer 1 · answered Nov 11 '16 at 14:36

0

Use a PIVOT.

SELECT roomid, [userId1] AS userId1, [userId2] AS userId2, [userId3] AS userId3, [userId4] AS userId4, [userId5] AS userId5
FROM (SELECT timestamp, userid, roomid
      FROM yourtable) a
PIVOT
(COUNT (timestamp) 
 FOR userid IN ([userId1], [userId2], [userId3], [userId4], [userId5])
) AS b
ORDER BY b.roomid;

OUTPUT:

roomid  userId1 userId2 userId3 userId4 userId5
roomid1 2       1       0       0       0
roomid2 0       0       1       1       1

SQL Fiddle: http://sqlfiddle.com/#!3/7df741/1/0

answered Nov 11 '16 at 14:36

Matt

14,906
27
99
149

i have about 500 user ids how can i do it without specifying each of them. also i don't need how many times he has been to the room just who so it shoul look like roomid 1 2 3 4 5 roomid1 userid1 userid2 userid3 roomid2 userid4 userid5 userid6 userid7 userid8 – Vladimir Ershov Nov 11 '16 at 14:44
As above, use a dynamic pivot, or specify them all. – Matt Nov 11 '16 at 14:45
check the discription example – Vladimir Ershov Nov 11 '16 at 14:48

who visited the room

1 Answers1