sqlexasolution

Put rows into columns

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.

Solution

  • 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