sql - T-SQL : Filtering columns by date

Question

我正在尝试根据事件日期生成列。我之前使用过 FULL JOIN，但即使在使用 CASE 时它也只会生成重复的行。

关于如何解决这个问题的一些指导会很好。表 1 是我当前的数据，Result 是我要查找的表。

Answer 1

您可以在动态 sql 上使用 PIVOT() 以允许 [date] 列中未知数量的日期在转置后用作列名。

由于您不能通过 MAX(activity) 直接对原始数据进行 PIVOT，MAX(activity) 只会为每个用户 ID 提供一行，因此您可以有一个内部子查询，它会引入另一列，即来自 ROW_NUMBER() 的 rnum。这将不允许在 userid 级别进行聚合并保持行数。

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

结果

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