Union Table by ignoring null values

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Union Table by ignoring null values

Author

Topic

karthic
Starting Member

1 Post

Posted - 2010-04-09 : 13:03:31

Hi,
i have a table which has the column values similar to the one below.

StdID StudenName REF1Name REF1ADD REF2NAME REF2ADD
001 Rahul Dr.Shiva aaaaa Dr.David bbbbbbbb
002 Govind Dr.Brown ccccc
003 Sachin Dr.willam dddd Dr.Thomas eeeeeeeee

I am using sql reporting services to generate a report for all students. Basically i am generating a letter for all the references for students. The reports will be generated for each row in the table.
I have to give a query which puts each reference in one row. But if the reference is null. it should not generate a row.I mean in the above example StdID 002 does not have REF2Name and REFADD the query should not generate row for this null REF2Name

StdID StudenName REF REFADD
001 Rahul Dr.Shiva aaaaa
001 Rahul Dr.David bbbbbbb
002 Govind Dr.Brown ccccc
003 Sachin Dr.willam dddd
003 sachin Dr.Thomas eeeeeeeee

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-09 : 13:16:12

Does this give you what you need?


SELECT StdID, StudenName, REF1Name AS REF, REF1ADD AS REFADD
FROM tableName
WHERE REF1Name IS NOT NULL

UNION SELECT StdID, StudenName, REF2NAME, REF2ADD
FROM tableName

WHERE REF2Name IS NOT NULL

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources