March 29, 2011 at 12:45 am
Hi,
Create table script,
create table abc_test2
(adr_club_name nvarchar(10) null,
adr_state nvarchar(10) null,
adr_country nvarchar(10)null,
adr_intl_provience nvarchar(10) null)
Insert Records in a table abc_test2
insert into abc_test2 values('ABC',NULL,'Australia','N.S.W.')
insert into abc_test2 values('BCD','BC','Canada',NULL)
insert into abc_test2 values('DBC','TX','United States',NULL)
insert into abc_test2 values('XYZ',NULL,'South Africa',NULL)
insert into abc_test2 values('UVW',NULL,'Argentina','Bs. As., Capital Federal')
insert into abc_test2 values('OPQ',NULL,'Brazil',NULL)
I want the result as per below Scenario,
if adr_state is null then I have to show there result
adr_club_name,adr_intl_provience,adr_country
and
If adr_intl_provience is null then I have to show there result
adr_club_name,adr_state,adr_country
and
If both adr_state and adr_intl_provience is null then I have to show there result
adr_club_name,adr_country
This is my SQL
SELECT
(adr_club_name+', '+ISNULL(adr_state,adr_intl_provience)
+', '+
(CASE WHEN (adr_state IS NULL AND adr_intl_provience IS NULL) THEN adr_country
END)) COLLATE SQL_Latin1_General_CP1_CI_AS Club_Name
FROM abc_test2
Please suggest me solution I have to implement this logic in my SP.
Best Regards,
Kiran R
March 29, 2011 at 3:06 am
Is this what you are after?
select adr_club_name +
coalesce(', ' + adr_state,'') +
coalesce(', ' + adr_country, '') +
coalesce( ', ' + adr_intl_provience,'')
from abc_test2
March 29, 2011 at 3:13 am
Hi
Your table script doesnt match the data you're inserting, field lengths need to be extended for that data.
I think you're after the coalesce function, something like this
select
adr_club_name+', '+coalesce(adr_state+', ',adr_intl_provience+', ','')+adr_country
from abc_test2
March 29, 2011 at 4:30 am
Hi,
Its worked...
Thanks for your help.
Kiran R
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply