Put rows into columns

Question

I have a table of these columns:

userID, date, NumberofLogins

Basically records on which date how many times the user has logged in.

How can I turn this into a table with the following one using SQL query?

UserID, NumberofLoginsDate1, NumberofLoginsDate2, ..., NumberofLoginsDate31.

I only have data for a months, so this should be fine.

Are there always 31 dates and how are they enumerated or, do you mean day of the month? — Jodrell, Sep 09 '13 at 08:23
Hi, fthiella, I'm using EXAplus. Client software for EXAsol a in-mememory database system. — user1552372, Sep 09 '13 at 08:26
There're always 31 dates available. They are day of the month. For the case now, I need the specific month, i.e. August of 2013. So it's fixed. — user1552372, Sep 09 '13 at 08:29
Do you want separate columns, or a single column with comma-separated values (that would be easier). — Simon Richter, Sep 09 '13 at 08:43

score 0 · Answer 1 · answered Sep 09 '13 at 08:40

0

Note: assumed for MySQL (hence use of Current_Date function, not available in all DBMS).

SELECT userID
     , Sum(CASE WHEN `date` = Current_Date THEN NumberofLogins END) As NumberofLoginsDate1
     , Sum(CASE WHEN `date` = DateAdd(dd, -1, Current_Date) THEN NumberofLogins END) As NumberofLoginsDate2
     , Sum(CASE WHEN `date` = DateAdd(dd, -2, Current_Date) THEN NumberofLogins END) As NumberofLoginsDate3
     , ...
     , Sum(CASE WHEN `date` = DateAdd(dd, -31, Current_Date) THEN NumberofLogins END) As NumberofLoginsDate31
FROM   your_table
GROUP
    BY userID

answered Sep 09 '13 at 08:40

gvee

16,732
35
50

Worked pretty well. My table is not huge, I don't know if there will be performance issue if the table is huge? – user1552372 Sep 09 '13 at 09:08
I'm afraid I am not familiar enough with EXAPlus to comment. However if this were in MySQL or SQL Server I would implement this method over the other option posted (with performance in mind). – gvee Sep 09 '13 at 09:17

Wietze314 · Accepted Answer · 2013-09-09T08:47:54.327

Could also be done by a pivot query. Repeat the Dx parts till 31. If your table has more data than August add a filter for that too. ( MONTH(date) = 8 )

SELECT 
U.userID
,D1.NumberofLogins AS NumberofLoginsDate1
,D2.NumberofLogins AS NumberofLoginsDate2
,Dx.NumberofLogins AS NumberofLoginsDatex
...

FROM (SELECT DISTINCT userID FROM tblLogins) U
LEFT JOIN (SELECT userID, NumberofLogins FORM tblLogins WHERE DAY(date) = 1) D1
ON U.userID = D1.userID
LEFT JOIN (SELECT userID, NumberofLogins FORM tblLogins WHERE DAY(date) = 2) D2
ON U.userID = D1.userID
LEFT JOIN (SELECT userID, NumberofLogins FORM tblLogins WHERE DAY(date) = x) Dx
ON U.userID = Dx.userID
...

OR

SELECT
userID
,SUM(CASE WHEN DAY(date) = 1 THEN NumberofLogins END) AS NumberofLoginsDate1
,SUM(CASE WHEN DAY(date) = 2 THEN NumberofLogins END) AS NumberofLoginsDate2
,SUM(CASE WHEN DAY(date) = x THEN NumberofLogins END) AS NumberofLoginsDatex
...

FROM tblLogins

WHERE MONTH(date) = 8

GROUP BY userID

Put rows into columns

2 Answers2