CODEWITHSUNDEEP
sqlnullpivotpivot-table
NewbieProgrammer
NewbieProgrammer

Reputation: 864

Why I am getting nulls in my SQL query?

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 :

enter image description here

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 :

enter image description here

Upvotes: 0

Views: 131

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239784

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

Upvotes: 1

Related Questions