SQL PIVOT查询不显示为null的行 [英] SQL PIVOT query not displaying row which is null

问题描述

这是我尝试执行的SQL查询：

This is the SQL query I am trying to execute :

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

现在这完全正常工作但是由于我的应用程序的设计，人员1,2中有时会有值3，有时只有1和2有价值，有时只有1，有时在任何一个都没有价值。



现在的问题是如果Person 1，Person 2和Person 3在所有这些中都为null，则查询将跳过整行。这是为什么？即使结果为空，我如何显示结果。 （如果重要的话，使用SQL SERVER 2012）

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)

推荐答案

将其更改为：

Change it to:

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

问题是你需要确保至少有tblVisitTicket表中有一条记录可供此查询使用。如果没有，则用于PIVOT源的子查询将返回零行。修复它的最好方法是左边用一个假数字表来加入它，它包含数字1,2,3，如下所示：

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:

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

这篇关于SQL PIVOT查询不显示为null的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！