时间戳

首页 > 解决方案 > 使用 PIVOT sqlserver

sql - 使用 PIVOT sqlserver

问题描述

我有下表：

表格1

COD_USER 日期 地位
1 2020-04-01 积极的
1 2020-06-30 不活跃
1 2020-12-01 积极的

有了这些数据，我需要生成以下输出：

表2

COD_USER 开始日期 结束日期
1 2020-04-01 2020-06-30
1 2020-12-01 无效的

我正在尝试为此使用 PIVOT，但我不知道这是否可行。

标签： sqlsql-serverdatetimepivot

解决方案

PIVOT 无法帮助的部分是将记录配对（一个开始/活动和一个结束/不活动）。

如果您知道您将始终在活动和非活动之间交替获取记录（因此永远不要获取active, active, inactive），并且始终以活动开头，这将为每对记录提供相同的序列号，您可以按该序列号进行分组。

WITH
  sorted AS
(
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY [COD_USER], [STATUS]
                           ORDER BY [DATE]
                      )
                        AS SEQ
  FROM
    Table1
)
SELECT
  [COD_USER],
  MAX(CASE WHEN [STATUS] =   'active' THEN [DATE] END)   AS START_DATE,
  MAX(CASE WHEN [STATUS] = 'inactive' THEN [DATE] END)   AS END_DATE
FROM
  sorted
GROUP BY
  [SEQ]

编辑

处理任何任意状态顺序的类似方法......

WITH
  sorted AS
(
  SELECT
    *,
    LAG([STATUS])
      OVER (PARTITION BY [COD_USER]
                ORDER BY [DATE]
      )
        AS PREV_STATUS,
    ROW_NUMBER()
      OVER (PARTITION BY [COD_USER]
                ORDER BY [DATE]
      )
        AS SEQ
  FROM
    Table1
)
SELECT
  [COD_USER],
  MAX(CASE WHEN [STATUS] =   'active' THEN [DATE] END)   AS START_DATE,
  MAX(CASE WHEN [STATUS] = 'inactive' THEN [DATE] END)   AS END_DATE
FROM
  sorted
GROUP BY
  [SEQ] - CASE WHEN [STATUS] = 'inactive' AND [PREV_STATUS] = 'active' THEN 1 ELSE 0 END

推荐阅读