CODEWITHSUNDEEP
sqlsql-server
ZedBee
ZedBee

Reputation: 2378

Pivot Sql Query Returns unwanted row

I am trying to understand the behavior of pivot table. Here is the query that I am executing

Declare @Students table (id int, name varchar(20))
insert into @Students VALUES(1, 'First Student')
insert into @Students VALUES(2, 'Second Student')
insert into @Students VALUES(3, 'Third Student')

Declare @Subjects table (id int, subject varchar(30))
insert into @Subjects VALUES (1, 'c#')
insert into @Subjects VALUES (2, 'Sql Server')
insert into @Subjects VALUES (3, 'Asp.net')
insert into @Subjects VALUES (4, 'Winforms')

Declare @StudentSubjects table (studentid int, subjectid int)
insert into @StudentSubjects VALUES(1,2)
insert into @StudentSubjects VALUES(1,3)
insert into @StudentSubjects VALUES(2,1)
insert into @StudentSubjects VALUES(2,2)

select name, [Sql Server], [Asp.net], [c#], [Winforms] from
(
    select st.name, su.[subject],
    Case When su.[subject] IS NULL OR st.name IS NULL THEN 'FALSE' ELSE 'TRUE' END as VALUE 

    FROM @Students st  FULL outer join @StudentSubjects ss on st.id = ss.studentid
    FULL Outer join @Subjects su on su.id = ss.subjectid
) as x
pivot
(
    MIN(value)
    for [subject] in ([Sql Server], [Asp.net], [c#], [Winforms])
)pv

the result set it returns is

enter image description here

I don't want the first row. How can I prevent this row to be returned. Also what is the best way to convert the Null values into false. On way that I tried is to use ISNULL() in select statement like

select name, ISNULL([Sql Server],'false') as [Sql Server] ...

Upvotes: 0

Views: 94

Answers (2)

Rohit Vipin Mathews
Rohit Vipin Mathews

Reputation: 11787

You can add awhere condition in your subquery or outer query where ever you feel its comfortable. 

select name, [Sql Server], [Asp.net], [c#], [Winforms] from
(
    select st.name, su.[subject],
    Case When su.[subject] IS NULL OR st.name IS NULL THEN 'FALSE' ELSE 'TRUE' END as VALUE 

    FROM @Students st  FULL outer join @StudentSubjects ss on st.id = ss.studentid
    FULL Outer join @Subjects su on su.id = ss.subjectid
) as x
pivot
(
    MIN(value)
    for [subject] in ([Sql Server], [Asp.net], [c#], [Winforms])
)pv
where name is not NULL

Or like how AK47 has mentioned

Upvotes: 1

AK47
AK47

Reputation: 3797

Try this, I have just added a where condition in you select statement.Hope it helps you!

select name, [Sql Server], [Asp.net], [c#], [Winforms] from
(
    select st.name, su.[subject],
    Case When su.[subject] IS NULL OR st.name IS NULL THEN 'FALSE' ELSE 'TRUE' END as VALUE 
    FROM @Students st  FULL outer join @StudentSubjects ss on st.id = ss.studentid
    FULL Outer join @Subjects su on su.id = ss.subjectid
    where st.name is not null
) as x

pivot
(
    MIN(value)
    for [subject] in ([Sql Server], [Asp.net], [c#], [Winforms])
)pv

Upvotes: 1

Related Questions