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

[quoted text, click to view]

As usual, this should really be done at the reporting layer.

[quoted text, click to view]

select No,
case when HomePhone = '' and WorkPhone = '' and Mobile = '' then ''
when HomePhone = '' and WorkPhone = '' then 'Mobile'
when HomePhone = '' and Mobile = '' then 'WorkPhone'
when WorkPhone = '' and Mobile = '' then 'HomePhone'
when HomePhone = '' then 'WorkPhone, Mobile'
when WorkPhone = '' then 'HomePhone, Mobile'
when Mobile = '' then 'HomePhone, WorkPhone'
else 'HomePhone, WorkPhone, Mobile'
end as DeviceTypes,
case when HomePhone = '' and WorkPhone = '' and Mobile = '' then ''
when HomePhone = '' and WorkPhone = '' then Mobile
when HomePhone = '' and Mobile = '' then WorkPhone
when WorkPhone = '' and Mobile = '' then HomePhone
when HomePhone = '' then WorkPhone + ', ' + Mobile
when WorkPhone = '' then HomePhone + ', ' + Mobile
when Mobile = '' then HomePhone + ', ' + WorkPhone
else HomePhone + ', ' + WorkPhone + ', ' + Mobile
end as DeviceInfo