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.DUse the same thinking as was suggested in my earlier reply:
SELECT
No
,'HomePhone, ' + CASE WHEN WorkPhone IS NOT NULL THEN 'WorkPhone' + ', ' ELS
E '' 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.googlegroups.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
>