SQL Server维度状态变化转换（纵表转横表）

原表及结果表如下图所示

做法：

1. 通过表自关联转换行列。
2. 根据NAME,FROM_DATE,STATUS使用开窗函数取最小的TO_DATE

with T1 AS (
SELECT 'A' AS NAME,'2020-01-01' AS [DATE],'active' AS STATUS 
UNION 
SELECT 'A' AS NAME,'2020-02-01' AS [DATE],'sleep' AS STATUS 
UNION 
SELECT 'A' AS NAME,'2020-02-26' AS [DATE],'active' AS STATUS 
UNION 
SELECT 'B' AS NAME,'2020-12-30' AS [DATE],'active' AS STATUS 
)
,T2 AS (
SELECT A.NAME
	,A.DATE AS FROM_DATE
	,COALESCE(B.DATE,'9999-12-31') AS TO_DATE
	,A.STATUS
	,row_number() OVER (PARTITION BY A.NAME,A.DATE,A.STATUS ORDER BY COALESCE(B.DATE,'9999-12-31') ASC) AS RN
FROM T1 A
LEFT JOIN T1 B ON A.NAME = B.NAME AND A.DATE < B.DATE)
SELECT NAME,FROM_DATE,TO_DATE,STATUS
FROM T2
WHERE RN = 1

也可以直接使用子查询，不要用CTE了

SELECT NAME,FROM_DATE,TO_DATE,STATUS
FROM (
	SELECT A.NAME
		,A.DATE AS FROM_DATE
		,COALESCE(B.DATE,'9999-12-31') AS TO_DATE
		,A.STATUS
		,row_number() OVER (PARTITION BY A.NAME,A.DATE,A.STATUS ORDER BY COALESCE(B.DATE,'9999-12-31') ASC) AS RN
	FROM T1 A
	LEFT JOIN T1 B ON A.NAME = B.NAME AND A.DATE < B.DATE) T
WHERE RN = 1