Posted by Erland Sommarskog on 05/23/07 07:35

Oonz (arundhaj@gmail.com) writes:
> The solution i have given is a static one.
> The above solution holds good when all the column have value in it.
> but when one column, say "HomePhone" is not available the resulting
> table should build as
>
>
> Id DeviceType DeviceInfo
> 20 Mobile, Email 323232232, t...@test.com
>
> please help me in this regards.

Use the CASE exprssion:

SELECT DeviceType = CASE WHEN HomePhone IS NOT NULL
THEN HomePhone + ','
ELSE ''
END +
CASE WHEN WorkPhone IS NOT NULL
THEN WorkdPhone + ','
ELSE ''
END ...

It gets a little devilish if the email is missing as you may end with a
trailing comma, but maybe you can live wity that.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]