Pivot table not displaying date values.

Question

Greetings experts.

The code below displays all the values in pivot table correctly with the exception of the date field.

For instance, we are trying the year or years an individual has submitted his or here report.

All the selected fields in the query display their values correctly except the value for dateCreated field is all NULL.

Below is sample of the data we get when we run the query below:

employeeNameempTitleemail2018201920222023Jane DoeSenior Assistant Attorney[@hotmail.com]NULLNULLNULLNULLKaren WiseInternal Auditor[@yahoo.com]NULLNULLNULLNULLAngel MaristDirector, Library[@gmail.com]NULLNULLNULLNULLKevin WisemanLibrary Director[@yahoo.com]NULLNULLNULLNULLMajor BatesDirector[@bing.com]NULLNULLNULLNULLMay BakerPRINCIPAL AUDITOR[@outlook.com]NULLNULLNULLNULL

ALTER PROCEDURE [dbo].[uspGetPivotedData]

AS
DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(FORMAT(d.dateCreated, 'yyyy')) 
            FROM DateDetails d
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT employeeName,empTitle,email, ' + @cols + ' from 
            (
                select employeeName
                    , empTitle
,email
                    , dateCreated,sourceincome
                from SourceDetails inner join Employees on SourceDetails.employeeID = Employees.EmployeeID
                inner join DateDetails on SourceDetails.employeeID = DateDetails.employeeID
           ) x
            pivot 
            (
                 max(sourceincome)
                for dateCreated in (' + @cols + ')
            ) p '

Any ideas what could be wrong?

Thanks in advance

Answer 1

Hello,

Thanks for posting your question in the Microsoft Q&A forum.

One possible reason for this issue can be that the column in your query has NULL values. Please check this, and if there is a NULL value in your table, Please handle them by using ISNULL or COALESCE within your pivot operation to avoid NULL columns in the result.

For example, you can handle it by your default value:

select ISNULL(date_column, 'defaault_Value') from * ...

It returns the 'default_value' if it is NULL.

Or you can use COALESCE:

                       COALESCE(dateCreated, ''N/A'') AS dateCreated, 

                       COALESCE(sourceincome, ''N/A'') AS sourceincome

Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful.

Best

Zahra

Answer 2

I've never learnt the PIVOT operator, since pivoting is better done with CASE in my opinion. But it seems that you have a mix. You set the column headers to be years, but in the pivot clause you are relating the full datetime values to the year. Don't you need

FORMAT(d.dateCreated, 'yyyy') in (' + @cols + ')