CODEWITHSUNDEEP
sqlsql-serverjoinpivotrow
محمد قاسم
محمد قاسم

Reputation: 179

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

Upvotes: 2

Views: 1769

Answers (1)

gofr1
gofr1

Reputation: 15977

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

Upvotes: 1

Related Questions