Ignoring the Null values - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Ignoring the Null values

Author

Topic

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-01-24 : 02:20:17

Hi SQLGurus,

The resulset I am getting from a query is like this

Bookingid user1 user2 user3 user4

6400 CalebKercheval Null Null Null
6400 Null HenryAssael Null Null
6454 KarenPark Null Null Null
6454 Null SachinNandanwar Null Null
6454 Null Null TejasVora Null
6454 Null Null Null DavidWu

I would like to have a output like this

Bookingid user1 user2 user3 user4

6400 CalebKercheval HenryAssael Null Null
6454 KarenPark SachinNandanwar TejasVora DavidWu

Thanks & Regards

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-24 : 02:46:54

[code]DECLARE @t table
(
Bookingid int,
user1 varchar(50),
user2 varchar(50),
user3 varchar(50),
user4 varchar(50)
)

INSERT INTO @t VALUES(6400 ,'CalebKercheval', Null, Null, Null)
INSERT INTO @t VALUES(6400 ,Null, 'HenryAssael', Null, Null)
INSERT INTO @t VALUES(6454, 'KarenPark', Null, Null, Null)
INSERT INTO @t VALUES(6454, Null, 'SachinNandanwar', Null, Null)
INSERT INTO @t VALUES(6454, Null ,Null, 'TejasVora', Null)
INSERT INTO @t VALUES(6454 ,Null, Null, Null, 'DavidWu')

SELECT t1.Bookingid,NULLIF(t2.list,'') as user1,
NULLIF(t3.list,'') as user2,
NULLIF(t4.list,'') as user3,
NULLIF(t5.list,'') as user4
FROM (SELECT DISTINCT Bookingid FROM @t) t1
CROSS APPLY(SELECT user1 AS [text()]
FROM @t
WHERE Bookingid=t1.Bookingid
FOR XML PATH(''))t2(list)
CROSS APPLY(SELECT user2 AS [text()]
FROM @t
WHERE Bookingid=t1.Bookingid
FOR XML PATH(''))t3(list)
CROSS APPLY(SELECT user3 AS [text()]
FROM @t
WHERE Bookingid=t1.Bookingid
FOR XML PATH(''))t4(list)
CROSS APPLY(SELECT user4 AS [text()]
FROM @t
WHERE Bookingid=t1.Bookingid
FOR XML PATH(''))t5(list)

Bookingid user1 user2 user3 user4
6400 CalebKercheval HenryAssael NULL NULL
6454 KarenPark SachinNandanwar TejasVora DavidWu[/code]

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2008-01-24 : 02:54:35

SELECT Bookinid,user1, user2,user3,user4
FROM tmp AS tmp_1 FULL OUTER JOIN
tmp AS tmp_2 ON tmp_1.id = tmp_2.id FULL OUTER JOIN
tmp AS tmp_3 ON tmp_1.id = tmp_3.id FULL OUTER JOIN
tmp ON tmp_3.id = tmp.id
WHERE (NOT (tmp.user1 IS NULL)) AND (NOT (tmp_3.user2 IS NULL)) AND (NOT (tmp_1.user3 IS NULL)) AND (NOT (tmp_2.user4 IS NULL))

-------------
TRY TO USE FULL OUTER JOIN WITH SAME TABLE AND IT WILL WORK

BEST OF LUCK

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources