问题描述:

Guys I am trying to use PIVOT in the SQL query and its working great with WHERE clause but am getting nulls in the result if I remove the WHERE clause from my SQL statement. I can't seem to understand why I am getting nulls as there are no nulls in my table at all.

I just want to display all the tickets generated.

SQL Query (With WHERE clause)

--TD

CREATE TABLE tblTicketDetail

(

TicketID INT,

IssuerName NVARCHAR(30),

TicketDescription NVARCHAR(30)

)

--EN

CREATE TABLE tblEngineer

(

EngineerID INT IDENTITY(1,1),

[Name] NVARCHAR(30)

)

--TE

CREATE TABLE tblTicket_Engineer

(

TicketID INT,

EngineerID INT

)

INSERT INTO tblTicketDetail (TicketID, IssuerName, TicketDescription)

VALUES(1, 'Saqib', 'qwerty keyboard')

INSERT INTO tblEngineer ([Name])

VALUES('Imran Khan'), ('Mubeen Khan'), ('Faraz Ahmed')

INSERT INTO tblTicket_Engineer (TicketID, EngineerID)

VALUES(1,1),(1,2),(1,3)

SELECT TicketID, IssuerName, TicketDescription, [1], [2], [3]

FROM (

SELECT TD.TicketID, TD.IssuerName, TD.TicketDescription, ROW_NUMBER() OVER(ORDER BY TE.EngineerID) AS EngineerNo, EN.[Name]

FROM tblTicketDetail AS TD

INNER JOIN tblTicket_Engineer AS TE ON TD.TicketID = TE.TicketID

INNER JOIN tblEngineer AS EN ON TE.EngineerID = EN.EngineerID

WHERE (TE.TicketID = 1)

) AS DT

PIVOT(MAX([Name]) FOR [EngineerNo] IN([1], [2], [3])) AS PT

DROP TABLE tblTicketDetail

DROP TABLE tblEngineer

DROP TABLE tblTicket_Engineer

Picture of Reference :

SQL Query (Without WHERE clause - displays nulls)

SELECT TicketID, IssuerName, TicketDescription, [1], [2], [3]

FROM (

SELECT TD.TicketID, TD.IssuerName, TD.TicketDescription, ROW_NUMBER() OVER(ORDER BY TE.EngineerID) AS EngineerNo, EN.[Name]

FROM tblTicketDetail AS TD

INNER JOIN tblTicket_Engineer AS TE ON TD.TicketID = TE.TicketID

INNER JOIN tblEngineer AS EN ON TE.EngineerID = EN.EngineerID

) AS DT

PIVOT(MAX([Name]) FOR [EngineerNo] IN([1], [2], [3])) AS PT

Picture for reference :

网友答案:

It's tricky to tell exactly what's going on (a useful debugging technique that you might be able to use is to comment out parts of the query, such as the PIVOT and do SELECT * to understand the before and after data), but I think you might want separate row numbers for each separate ticket. In which case you want PARTITION BY in your ROW_NUMBER() call:

SELECT  TicketID, IssuerName, TicketDescription, [1], [2], [3]
FROM (
    SELECT TD.TicketID, TD.IssuerName, TD.TicketDescription,
          ROW_NUMBER() OVER(
             PARTITION BY TD.TicketID
             ORDER BY TE.EngineerID) AS EngineerNo,
          EN.[Name]
    FROM  tblTicketDetail AS TD
        INNER JOIN tblTicket_Engineer AS TE ON TD.TicketID = TE.TicketID 
        INNER JOIN tblEngineer AS EN ON TE.EngineerID = EN.EngineerID 
) AS DT
PIVOT(MAX([Name]) FOR [EngineerNo] IN([1], [2], [3])) AS PT
相关阅读:
Top