CODEWITHSUNDEEP
sqlsql-serverjoin
aeroalpha
aeroalpha

Reputation: 35

T-SQL: Filtering columns by date

I am trying to generate columns based on the date of the activity. I used FULL JOIN before but it only generated duplicated rows even when using CASE.

Some guidance on how to approach this would be great. Table 1 is my current data and Result is the table I am looking for.

enter image description here

Upvotes: 3

Views: 73

Answers (2)

Ferdinand Gaspar
Ferdinand Gaspar

Reputation: 2063

You can use PIVOT() on dynamic sql to allow the unknown number of dates in the [date] column to be used as column names after transposition.

Since you cannot directly PIVOT the raw data by having MAX(activity) which will give only one row each userid, you can have an inner sub-query that will introduce another column, rnum from ROW_NUMBER(). This will not allow the aggregation in userid level and maintain the number of rows.

DECLARE @cols  AS NVARCHAR(MAX),
        @query AS NVARCHAR(MAX)

SET @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME([date]) 
                     FROM table1 t -- use your table name
                      FOR XML PATH(''), TYPE
                  ).value('.', 'NVARCHAR(MAX)'),1,1,'')

SET @query = 'SELECT userid, ' + @cols + '
                FROM (SELECT userid, rnum, ' + @cols + '
                        FROM (SELECT *,
                                     ROW_NUMBER() OVER (PARTITION BY userid, 
                                                                     [date] 
                                                            ORDER BY [date]) rnum
                                FROM table1) x
                        PIVOT (MAX(activity)
                          FOR [date] IN (' + @cols +')) p
                      ) t
               ORDER BY userid'

EXECUTE(@query)
GO

Result

userid  2018-09-10  2018-09-11  2018-09-12
1       gym         run         gym
1       run         NULL        run
1       push-ups    NULL        NULL
2       gym         NULL        gym
3       run         NULL        run
4       gym         push-ups    gym
4       NULL        run         NULL

Upvotes: 2

Guru0008
Guru0008

Reputation: 54

SELECT userid, date, activity
FROM table1
WHERE date = '9/10/2018'
UNION ALL
SELECT userid, date, activity
FROM table1
WHERE date = '9/11/2018'
UNION ALL
SELECT userid, date, activity
FROM table1
WHERE date = '9/12/2018';

Upvotes: 0

Related Questions