sql-server - 如何使用 Joins 和 Groupby 编写 PIVOT 查询

Question

嗨，我创建了一个 MS SQL 查询，它正在生成完美的结果。但我需要更改行和列的值。我在互联网上搜索并了解了 PIVOT 来改变方向。我已经创建了一些小查询，但我无法将它与连接和组一起使用，任何帮助将不胜感激。

这是我的查询和输出

SELECT AttendanceDate,count(attendance)AS attendanceCount,AttOPt.Name AS AttendanceStatus,emp.Name AS MarkedBy FROM Attendance
LEFT OUTER JOIN AttendanceOption AttOPt
ON AttOPt.ID=Attendance
LEFT OUTER JOIN Employee emp
on MarkedBy=emp.Id
GROUP BY AttendanceDate,Attendance,MarkedBy,AttOPt.Name,emp.Name

我喜欢这样的输出:

AttendanceDate  Present Absent  Half Day    WithoutNotification MarkedBy
14-08-2013      11       0      0            0                  Anuj Koundal
30-08-2013      4        3      2            2                  Anuj Koundal

Answer 1

试试这个——

SELECT * 
FROM ( 
    SELECT  AttendanceDate
        ,   attendanceCount = COUNT(attendance) 
        ,   AttendanceStatus = AttOPt.name 
        ,   MarkedBy = emp.name 
    FROM Attendance
    LEFT JOIN AttendanceOption AttOPt ON AttOPt.id = Attendance
    LEFT JOIN Employee emp ON MarkedBy = emp.id
    GROUP BY
        AttendanceDate
    ,   Attendance
    ,   AttOPt.name
    ,   emp.name
) t
PIVOT 
(
    SUM(attendanceCount) 
    FOR AttendanceStatus IN ([Present], [Absent], [Half Day], [WithoutNotification])
) p
ORDER BY AttendanceDate DESC

更新:

-- variant #1

SELECT 
      AttendanceDate
    , MarkedBy 
    , [Present] = ISNULL([Present], 0)
    , [Absent] = ISNULL([Absent], 0)
    , [Half Day] = ISNULL([Half Day], 0)
    , [WithoutNotification] = ISNULL([WithoutNotification], 0)
FROM ( 
    ...
) t
PIVOT 
(
    ...
) p

-- variant #2

SELECT *
FROM ( 
    SELECT  
          AttendanceDate
        , attendanceCount = COUNT(attendance) 
        , AttendanceStatus = AttOPt.name 
        , MarkedBy = emp.name 
    FROM Attendance
    JOIN AttendanceOption AttOPt ON AttOPt.id = Attendance
    JOIN Employee emp ON MarkedBy = emp.id
    GROUP BY ALL
          AttendanceDate
        , Attendance
        , AttOPt.name
        , emp.name
) t
PIVOT 
(
    ...
) p

更新 2:

DECLARE @Columns NVARCHAR(MAX)
SELECT @Columns = STUFF((
    SELECT DISTINCT ', [' + a.name + ']'
    FROM dbo.Attendance t
    JOIN dbo.AttendanceOption a ON a.id = t.Attendance
    FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 2, '')

DECLARE @ColumnsNULLs NVARCHAR(MAX)
SELECT @ColumnsNULLs = STUFF((
    SELECT DISTINCT ', [' + a.name + '] = ISNULL([' + a.name + '], 0)'
    FROM dbo.Attendance t
    JOIN dbo.AttendanceOption a ON a.id = t.Attendance
    FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 2, '')

DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = '
SELECT AttendanceDate, ' + @ColumnsNULLs + ', MarkedBy
FROM ( 
    SELECT  AttendanceDate
        ,   attendanceCount = COUNT(attendance) 
        ,   AttendanceStatus = AttOPt.name 
        ,   MarkedBy = emp.name 
    FROM Attendance
    LEFT JOIN AttendanceOption AttOPt ON AttOPt.id = Attendance
    LEFT JOIN Employee emp ON MarkedBy = emp.id
    GROUP BY
        AttendanceDate
    ,   Attendance
    ,   AttOPt.name
    ,   emp.name
) t
PIVOT 
(
    SUM(attendanceCount) 
    FOR AttendanceStatus IN (' + @Columns + ')
) p
ORDER BY AttendanceDate DESC'

PRINT @SQL
EXEC sys.sp_executesql @SQL    

输出 -

AttendanceDate          Absent      Half Day    Present     Without Notification MarkedBy
----------------------- ----------- ----------- ----------- -------------------- ---------------
2013-08-30 00:00:00.000 3           2           4           2                    Anuj Kaundal
2013-08-14 00:00:00.000 0           0           11          0                    Anuj Kaundal