我有一张简单的桌子:
CREATE TABLE ActivityHX (
ITEM_ID nvarchar(5),
LINE nvarchar(2),
ACTIVITY_DATE date,
ACTIVITY nvarchar(8)
);
INSERT INTO ActivityHX (ITEM_ID, LINE, ACTIVITY_DATE, ACTIVITY)
VALUES
('38003', '1', '20230803', 'OPENED'),
('38003', '2', '20230803', 'CLOSED'),
('38003', '3', '20230812', 'REOPENED'),
('38003', '4', '20230814', 'CLOSED');
这就得出了这样的结果:
| ITEM_ID | LINE | ACTIVITY_DATE | ACTIVITY |
|---|---|---|---|
| 38003 | 1 | 2023-08-03 | OPENED |
| 38003 | 2 | 2023-08-03 | CLOSED |
| 38003 | 3 | 2023-08-12 | REOPENED |
| 38003 | 4 | 2023-08-14 | CLOSED |
我在这里开始拉小提琴:https://dbfiddle.uk/f9tBIRFz.我正在试图弄清楚如何让我的数据看起来像这样:
| ITEM_ID | START_DATE | END_DATE |
|---|---|---|
| 38003 | 2023-08-03 | 2023-08-03 |
| 38003 | 2023-08-12 | 2023-08-14 |
我的数据很简单，遵循一个干净的顺序，大多数项目只有一个开放和关闭日期.但是，当我有多个重新打开日期的项目时，我知道下一行将是关闭的活动.我正在try 解决如何在不获取空值的情况下将数据转置到开始日期和结束日期列.当我try 使用row_number()时，即使我try 在其中使用CASE语句，也会得到空值.
SELECT
ITEM_ID
,CASE WHEN ACTIVITY IN ('OPENED', 'REOPENED') THEN ACTIVITY_DATE END 'START_DATE'
,CASE WHEN ACTIVITY = 'CLOSED' THEN ACTIVITY_DATE END 'END_DATE'
FROM (
SELECT
ITEM_ID
,LINE
,ACTIVITY_DATE
,ACTIVITY
,Diff as 'Days Diff'
,ROW_NUMBER() OVER (PARTITION BY ITEM_ID ORDER BY CASE WHEN Diff <= 0 then -1 else 1 end, abs(Diff)) as RN
from mydata) ActivityHX
我已经搜索了各种解决方案，并试图将其拼凑在一起，但我无法将表格展平到只有两行.谢谢你的建议.