constructing strings from table

Hi friends,
please help me in selecting values from the table
the record is as follows:
Id HomePhone WorkPhone Mobile Email
20 2323223 323232232 test@.test.com
i have to select values as follows.
Id DeviceType DeviceInfo
20 HomePhone, Mobile, Email 2323223, 323232232, test@.test.com
the one solution is:
select
'HomePhone, Mobile, Email' AS DeviceType
HomePhone + ',' + WorkPhone + ',' + MobilePhone + ',' + Email AS
DeviceInfo
from table where Id = 20
but here the work phone number is not available so that information
has to be truncated...
Thanks in Advance
Arunkumar.D
Use the same thinking as was suggested in my earlier reply:
SELECT
No
,'HomePhone, ' + CASE WHEN WorkPhone IS NOT NULL THEN 'WorkPhone' + ', ' ELSE '' END + 'Mobile' AS
DeviceType
,HomePhone + ', ' + COALESCE(WorkPhone + ', ', '') + Mobile
FROM
(
SELECT 20 AS No, '23232' AS HomePhone, NULL AS WorkPhone, '98327' AS Mobile
) AS x
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Oonz" <arundhaj@.gmail.com> wrote in message
news:1179848438.242226.4580@.n15g2000prd.googlegrou ps.com...
> Hi friends,
> please help me in selecting values from the table
> the record is as follows:
> Id HomePhone WorkPhone Mobile Email
> 20 2323223 323232232 test@.test.com
> i have to select values as follows.
> Id DeviceType DeviceInfo
> 20 HomePhone, Mobile, Email 2323223, 323232232, test@.test.com
>
> the one solution is:
> select
> 'HomePhone, Mobile, Email' AS DeviceType
> HomePhone + ',' + WorkPhone + ',' + MobilePhone + ',' + Email AS
> DeviceInfo
> from table where Id = 20
> but here the work phone number is not available so that information
> has to be truncated...
> Thanks in Advance
> Arunkumar.D
>