带枢轴和分组内连接的SQL SERVER查询

Question

我已经尝试过PIVOT方法和CASE WHEN，但是我还没有想出如何从我的表中得到这个结果。

这就是我们所需要的：

personnel_id  SATURDAY SUNDAY  MONDAY   TUESDAY   WEDNESDAY THURSDAY  FRIDAY
Juan Jose       OFF     OFF     6-2        6- 2      OFF      OFF      6-2

从这些表格中：

[TABLES FROM SQL SERVER][1]

到目前为止，我已经：

SELECT  
    personnel_name, 
    CASE WHEN day_name = 'saturday' then entrada END AS SAT,
    CASE WHEN day_name = 'sunday' then entrada END AS SUN,
    CASE WHEN day_name = 'monday' then entrada END AS MON,
    CASE WHEN day_name = 'tuesday' then entrada END AS TUE,
    CASE WHEN day_name = 'wednesday' then entrada END AS WED,
    CASE WHEN day_name = 'thursday' then entrada END AS THUR,
    CASE WHEN day_name = 'friday' then entrada END AS FRI
FROM
   (SELECT 
        personnel_name, tbl_shifts.entrada, tbl_days.day_name
    FROM 
        tbl_personnel_weekshift 
    INNER JOIN 
        tbl_personnel ON tbl_personnel_weekshift.personnel_id = tbl_personnel.personnel_id
    INNER JOIN 
        tbl_shifts ON tbl_personnel_weekshift.shift_id = tbl_shifts.shift_id
    INNER JOIN 
        tbl_days ON tbl_personnel_weekshift.day_id = tbl_days.day_id) AS IIIPO;

返回以下结果：

结果

Juan Jose Moreno OFF    NULL    NULL NULL   NULL    NULL    NULL
Juan Jose Moreno NULL   OFF     NULL NULL   NULL    NULL    NULL
Juan Jose Moreno NULL   NULL    6-2  NULL   NULL    NULL    NULL
Juan Jose Moreno NULL   NULL    NULL 6-2    NULL    NULL    NULL
Juan Jose Moreno NULL   NULL    NULL NULL   OFF     NULL    NULL
Juan Jose Moreno NULL   NULL    NULL NULL   NULL    OFF     NULL
Juan Jose Moreno NULL   NULL    NULL NULL   NULL    NULL    6-2

我需要找到一种很好的方法，或者找到一种只按一行分组的方法，并去掉空值。

谢谢你们的时间和努力

Answer 1

我想你只是想要有条件的聚合：

SELECT personnel_name, 
       MAX(CASE WHEN day_name = 'saturday' then entrada END) AS SAT,
       MAX(CASE WHEN day_name = 'sunday' then entrada END) AS SUN,
       MAX(CASE WHEN day_name = 'monday' then entrada END) AS MON,
       MAX(CASE WHEN day_name = 'tuesday' then entrada END) AS TUE,
       MAX(CASE WHEN day_name = 'wednesday' then entrada END) AS WED,
       MAX(CASE WHEN day_name = 'thursday' then entrada END) AS THUR,
       MAX(CASE WHEN day_name = 'friday' then entrada END) AS FRI
FROM (SELECT personnel_name, tbl_shifts.entrada, tbl_days.day_name
      FROM tbl_personnel_weekshift INNER JOIN
           tbl_personnel
           ON tbl_personnel_weekshift.personnel_id = tbl_personnel.personnel_id INNER JOIN
           tbl_shifts
           ON tbl_personnel_weekshift.shift_id = tbl_shifts.shift_id INNER JOIN
           tbl_days
           ON tbl_personnel_weekshift.day_id = tbl_days.day_id
     ) IIIPO
GROPU BY personnel_name;