SQL PIVOT query not displaying row which is null

Question

4.20/5 (2 votes)

See more:

SQL

pivot-table

pivot

, +

This is the SQL query I am trying to execute :

SQL

SELECT
    VisitID,
    VisitDate,
    IssuedBy,
    VisitPurpose,
    CompanyName,
    SPname as 'SalePerson',
    [1] as 'Person 1',
    [2] as 'Person 2',
    [3] as 'Person 3'
FROM (
        SELECT
            TD.VisitID,
            TD.VisitDate,
            TD.IssuedBy,
            TD.VisitPurpose,
            TC.CompanyName,
            SP.SPname,
            ROW_NUMBER() OVER(PARTITION BY TD.VisitID ORDER BY TE.AlongwithID) AS EngineerNo,
            EN.[AlongwithName]
        FROM
            tblVisitTicket AS TD
            INNER JOIN tblVisit_Alongwith AS TE
                ON TD.VisitID = TE.VisitID
            INNER JOIN tblAlongWith AS EN
                ON TE.AlongwithID = EN.AlongwithID
            INNER JOIN tblCompany AS TC
                ON TD.CompanyID = TC.CompanyID
            INNER JOIN tblSalePerson AS SP
                ON TD.SalePersonID = SP.SalePersonID
        WHERE TD.VisitStatus = 1
    ) AS DT
    PIVOT(MAX([AlongwithName])
        FOR [EngineerNo]
        IN([1], [2], [3])
    ) AS PT

Now this is working perfectly however due to the design of my application there will be sometimes values in Person 1, 2 and 3 and sometimes there will be value in only 1 and 2 and sometimes in only 1 and sometimes there will be no value in any of them.

Now the problem is that if Person 1, Person 2 and Person 3 has null in all of them, the whole row will be skipped by the query. Why is that? How do I display result even when there is null in them. (Using SQL SERVER 2012 if it matters)

Posted 22-Apr-14 20:47pm

Shajee Afzal

Updated 22-Apr-14 20:49pm

v2

Add a Solution

Comments

Herman<T>.Instance 23-Apr-14 5:51am

tried LEFT JOIN in stead of INNER JOIN?

2 solutions

Solution 2

The problem is that you need to make sure that there is at least one record in your tblVisitTicket table for this query to work. If there are none the subquery you use for PIVOT source will return zero rows. The best way to fix it is to LEFT JOIN it with a fake numbers table that will contain numbers 1, 2, 3, like this:

SQL

SELECT
    VisitID,
    VisitDate,
    IssuedBy,
    VisitPurpose,
    CompanyName,
    SPname as 'SalePerson',
    [1] as 'Person 1',
    [2] as 'Person 2',
    [3] as 'Person 3'
FROM (
        SELECT
            WK.VisitID,
            WK.VisitDate,
            WK.IssuedBy,
            WK.VisitPurpose,
            WK.CompanyName,
            WK.SPname,
            N.n AS EngineerNo,
            WK.[AlongwithName]
        FROM
        (SELECT 1 AS n UNION ALL SELECT 2 AS n UNION ALL SELECT 3 AS n ) N -- this is your fake numbers table
        LEFT JOIN
        (SELECT
            TD.VisitID,
            TD.VisitDate,
            TD.IssuedBy,
            TD.VisitPurpose,
            TC.CompanyName,
            SP.SPname,
            ROW_NUMBER() OVER(PARTITION BY TD.VisitID ORDER BY TE.AlongwithID) AS EngineerNo,
            EN.[AlongwithName]
        FROM
            tblVisitTicket AS TD
            INNER JOIN tblVisit_Alongwith AS TE
                ON TD.VisitID = TE.VisitID
            INNER JOIN tblAlongWith AS EN
                ON TE.AlongwithID = EN.AlongwithID
            INNER JOIN tblCompany AS TC
                ON TD.CompanyID = TC.CompanyID
            INNER JOIN tblSalePerson AS SP
                ON TD.SalePersonID = SP.SalePersonID
        WHERE TD.VisitStatus = 1 ) WK ON N.n = WK.EngineerNo
    ) AS DT
    PIVOT(MAX([AlongwithName])
        FOR [EngineerNo]
        IN([1], [2], [3])
    ) AS PT

Posted 28-Apr-14 13:47pm

chaau

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2014-04-28T11:54:00

Change it to:

SQL

SELECT VisitID, VisitDate, IssuedBy, VisitPurpose, CompanyName, SPname as 'SalePerson', [1] as 'Person 1', [2] as 'Person 2', [3] as 'Person 3'
FROM (
SELECT VisitID, VisitDate, IssuedBy, VisitPurpose, CompanyName, SPname, [1], [2], [3] 
FROM (
        SELECT TD.VisitID, TD.VisitDate, TD.IssuedBy, TD.VisitPurpose, TC.CompanyName, SP.SPname,
            ROW_NUMBER() OVER(PARTITION BY TD.VisitID ORDER BY TE.AlongwithID) AS EngineerNo,
            EN.[AlongwithName]
        FROM
            tblVisitTicket AS TD
            INNER JOIN tblVisit_Alongwith AS TE
                ON TD.VisitID = TE.VisitID
            INNER JOIN tblAlongWith AS EN
                ON TE.AlongwithID = EN.AlongwithID
            INNER JOIN tblCompany AS TC
                ON TD.CompanyID = TC.CompanyID
            INNER JOIN tblSalePerson AS SP
                ON TD.SalePersonID = SP.SalePersonID
        WHERE TD.VisitStatus = 1
    ) AS DT
    PIVOT(MAX([AlongwithName]) FOR [EngineerNo] IN([1], [2], [3])) AS PT
) AS MT