constructing strings from table

Hi friends,
i have to select values from the table consider with the following
record:
No HomePhone WorkPhone Mobile
20 23232 98327
the selection have to be done as:
No DeviceType DeviceInfo
20 HomePhone, Mobile 23232, 98327
since there is no value for workphone it have to be removed in both
devicetype and deviceinfo column.
Thanks
Arunkumar.DNo DDL = not tested...
Something like (assuming NULL when you say "no value"):
SELECT
No
,'HomePhone, ' + CASE WHEN WorkPhone IS NULL THEN 'WorkPhone' + ', ' ELSE ''
END + 'Mobile' AS
DeviceType
,HomePhone + ', ' + COALESCE(WorkPhone + ', ', '') + Mobile
FROM ...
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:1179849918.549063.61900@.n15g2000prd.googlegroups.com...
> Hi friends,
> i have to select values from the table consider with the following
> record:
> No HomePhone WorkPhone Mobile
> 20 23232 98327
> the selection have to be done as:
> No DeviceType DeviceInfo
> 20 HomePhone, Mobile 23232, 98327
> since there is no value for workphone it have to be removed in both
> devicetype and deviceinfo column.
> Thanks
> Arunkumar.D
>|||> i have to select values from the table consider with the following
> record:
> No HomePhone WorkPhone Mobile
> 20 23232 98327
> the selection have to be done as:
> No DeviceType DeviceInfo
> 20 HomePhone, Mobile 23232, 98327
What about something like:
SELECT No,
CASE WHEN HomePhone IS NULL THEN '' ELSE 'HomePhone' END +
CASE WHEN WorkPhone IS NULL THEN '' ELSE 'WorkPhone' END +
CASE WHEN Mobile IS NULL THEN '' ELSE 'Mobile' END
AS DeviceType,
CASE WHEN HomePhone IS NULL THEN '' ELSE HomePhone END +
CASE WHEN WorkPhone IS NULL THEN '' ELSE WorkPhone END +
CASE WHEN Mobile IS NULL THEN '' ELSE Mobile END
AS DeviceInfo
FROM TableName
(You will need to add a bit of logic to work out where to put the commas)
Rgds,
Gavin