How to write PIVOT query with Joins and Groupby

Question

Hi I have created a MS SQL query it is generating perfect results. But I need to change the values of rows and columns. I have searched over internet and learned about PIVOT to change the orientation. I have created some small queries but I am not able to use it with joins and group by any help will be appreciated.

Here is my query and output

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

enter image description here

I like output like this:

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

refer : [link](http://stackoverflow.com/questions/9802057/pivot-data-in-sql-server?rq=1) — Ramakrishna.p, Aug 19 '13 at 07:28

Devart · Accepted Answer · 2013-08-19T10:04:19.240

Try this one -

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

Update:

-- 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

Update 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

Output -

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

Great solution only small thing left how to replace NULL by 0 in Present, Absent.. column — MaxPayne, Aug 19 '13 at 07:54
One more question : Can we make [Present], [Absent], [Half Day], [WithoutNotification] these columns dynamic? — MaxPayne, Aug 19 '13 at 09:10
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/35735/discussion-between-maxpayne-and-devart) — MaxPayne, Aug 19 '13 at 09:50

How to write PIVOT query with Joins and Groupby

1 Answers1