QASIM ASHRAF QASIM ASHRAF - 3 months ago 10
SQL Question

SQL Pivot - How To Show all rows with null values?

Is there a way using Pivot to include rows with null Values?

Ideally, I'd want the output like this

enter image description here

However, Currently Pivot is not Returning Empty Rows and I'm getting output like this

enter image description here

I'm using the code below to get the result set below that:

SELECT
ID,
Name,
Design,
month
FROM (SELECT
E.EnrollId AS ID,
E.EmployeeName AS Name,
D.Designation AS
Design,
DATENAME(MONTH, La.Date) AS month,
DATENAME(DAY, La.Date) AS Day,
L.LeaveShortName AS Short
FROM Employee E
JOIN LeaveApplicationDates LA
ON E.EnrollId = LA.EnrollId
JOIN Leave L
ON L.LeaveId = LA.LeaveId
JOIN Designation D
ON E.DesignationId = D.DesignationId
WHERE (E.EnrollId = '10277')
AND (La.Date BETWEEN '1/1/2016' AND '8/1/2016')) AS setr
PIVOT
(
MAX(Short)
FOR [Day] IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10],
[11], [12], [13], [14], [15], [16], [17], [18], [19], [20],
[21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31])
) AS pivotTable


I follow this but solution not working

Answer

You can use CTE to obtain table with month names:

;WITH cte AS (
    SELECT  CAST('2016-01-01' as datetime) as d
    UNION ALL
    SELECT  DATEADD(month,1,d)
    FROM cte
    WHERE DATEPART(month,d) < 12
)

SELECT  DATEPART(month,d) as month_num,
        DATENAME(month,d) as month_name
FROM cte

Output:

month_num   month_name
1           January
2           February
3           March
4           April
5           May
6           June
7           July
8           August
9           September
10          October
11          November
12          December

And put it in your query, like:

;WITH cte AS (
    SELECT  CAST('2016-01-01' as datetime) as d
    UNION ALL
    SELECT  DATEADD(month,1,d)
    FROM cte
    WHERE DATEPART(month,d) < 12
)

SELECT  ID,
        Name,
        Design,
        [month],
        [1], [2], [3], [4], [5], [6], [7], [8], [9], [10],
[11], [12], [13], [14], [15], [16], [17], [18], [19], [20],
[21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31]
FROM (
    SELECT  E.EnrollId AS ID,
            E.EmployeeName AS Name,
            D.Designation AS
            Design,
            month_num,
            month_name AS [month],
            DATENAME(DAY, La.[Date]) AS [Day],
            L.LeaveShortName AS Short
    FROM Employee E
    CROSS JOIN (
        SELECT  DATEPART(month,d) as month_num,
                DATENAME(month,d) as month_name
        FROM cte
        ) as mt
    LEFT JOIN LeaveApplicationDates LA
        ON E.EnrollId = LA.EnrollId and mt.month_name = DATENAME(MONTH, La.[Date]) and La.[Date] BETWEEN '1/1/2016' AND '8/1/2016'
    LEFT JOIN Leave L
        ON L.LeaveId = LA.LeaveId
    LEFT JOIN Designation D
        ON E.DesignationId = D.DesignationId
) AS setr
PIVOT
(
MAX(Short)
FOR [Day] IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10],
[11], [12], [13], [14], [15], [16], [17], [18], [19], [20],
[21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31])
) AS pivotTable
WHERE ID = '10277'
ORDER BY ID, month_num