SQL如何对按ID分组的重复日期范围进行数据透视

Question

我需要帮助把数据从桌子上转过来。我有一个休假请求表

CREATE TABLE EmpVacRequest
(EmpID int, VacReqPri int , StartDate date, EndDate date)

我用样本数据播撒它

INSERT INTO EmpVacRequest 
(EmpID,VacReqPri,StartDate,EndDate) VALUES (100,1,'2016-1-1','2016-1-3')

INSERT INTO EmpVacRequest 
(EmpID,VacReqPri,StartDate,EndDate) VALUES (100,2,'2016-2-4','2016-2-8')

INSERT INTO EmpVacRequest 
(EmpID,VacReqPri,StartDate,EndDate) VALUES (200,1,'2016-1-2','2016-1-5')

INSERT INTO EmpVacRequest 
(EmpID,VacReqPri,StartDate,EndDate) VALUES (200,2,'2016-2-1','2016-2-3')

我需要的输出如下所示：

EmpId       Vac1Start      Vac1End     Vac2Start   Vac2End
100         2016-1-1       2016-1-3    2016-2-4    2016-2-8
200         2016-1-2       2016-1-5    2016-2-1    2016-2-3

每个员工只有4个假期请求，优先级为1-4，所以我想也许我应该创建一个带有硬编码列的新表，并使用游标或CTE填充它，但我希望有人能有一个更优雅的解决方案。

我有一个SQLFiddle在这里，如果它有助于提供解决方案的话。

Answer 1

以下是对2的查询。您可以轻松地将其扩展到4。

SQL Fiddle

WITH EMPVACREQUESTCTE AS
(
SELECT VACREQPRI AS RN,* FROM EMPVACREQUEST
)

SELECT T1.EMPID,MAX(STARTDATE1) AS VAC1START,MAX(ENDDATE1) AS VAC1END,MAX(STARTDATE2) AS VAC2START,MAX(ENDDATE2) AS VAC1END FROM
(
    SELECT RN,EMPID,MAX([1]) STARTDATE1,MAX([2]) STARTDATE2 FROM
    (
        SELECT * FROM EMPVACREQUESTCTE
        PIVOT
        (MAX(STARTDATE) FOR VACREQPRI IN ([1],[2])) P
    ) K
    GROUP BY EMPID,RN
)T1 
INNER JOIN
(
    SELECT RN,EMPID,MAX([1]) ENDDATE1,MAX([2]) ENDDATE2 FROM
    (
        SELECT * FROM EMPVACREQUESTCTE
        PIVOT
        (MAX(ENDDATE) FOR VACREQPRI IN ([1],[2])) P
    ) K
    GROUP BY EMPID,RN
)T2 
   ON T1.EMPID = T2.EMPID AND T1.RN = T2.RN
GROUP BY T1.EMPID

Answer 2

因为黑暗骑士已经加入了他的解决方案，我想我应该再加一个，

你也可以尝试：

select 
      empid, 
      max(case  when VacReqPri = 1 then startdate end) as vac1start,
      max(case  when VacReqPri = 1 then enddate end) as vac1end,
      max(case  when VacReqPri = 2 then startdate end) as vac2start,
      max(case  when VacReqPri = 2 then enddate end) as vac2end
from EmpVacRequest group by empid