January 19, 2006 at 3:50 pm
Hello All,
Im very new to programming with SQL Server, and I need some code to do this:
I have a table... a BIG table that has names, and a field for every STATE (name, ak, al, ar, az, ca...) you get the idea. I need to generate a string that will look at all of those state fields, and if they are not null fields then add them to a variable?... that can be displayed. Does that make sense
So each could have all 50, or they could have none, but the string for each would only display the fields which are not NULL..
The output needs to be something like this
Name, ak, ca
Name, al, ar, az, ca
Name, ak, az, ca
.
.
.
I hope Im describing that ok. none the less, I am a novice at this, and I need to do this rather quiclky, so I thought I would drop it out here to see if anybody has an idea.
Thanks in Advance,
Ed
January 20, 2006 at 6:07 am
something like ..
create table tblTest
(Name char(20),
state1 char(2),
state2 char(2),
state3 char(2),
state4 char(2))
insert into tblTest values ('Fred',NULL,NULL,'CA',NULL)
insert into tblTest values ('Bill',NULL,'AZ','NY',NULL)
insert into tblTest values ('Joe',NULL,'CA',NULL,'WA')
SELECT Name +
CASE WHEN State1 IS NULL THEN ''
ELSE State1 + ', '
END +
CASE WHEN State2 IS NULL THEN ''
ELSE State2 + ', '
END +
CASE WHEN State3 IS NULL THEN ''
ELSE State3 + ', '
END +
CASE WHEN State4 IS NULL THEN ''
ELSE State4 + ', '
END
AS 'Data'
FROM tblTest
But you have to lose the trailing ',' somehow
January 20, 2006 at 9:11 am
create table #tblTest
(Name char(20),
state1 char(2),
state2 char(2),
state3 char(2),
state4 char(2))
insert into #tblTest values ('Fred',NULL,NULL,'CA',NULL)
insert into #tblTest values ('Bill',NULL,'AZ','NY',NULL)
insert into #tblTest values ('Joe',NULL,'CA',NULL,'WA')
SELECT rtrim(Name)
+ isnull(', ' + State1, '')
+ isnull(', ' + State2, '')
+ isnull(', ' + State3, '')
+ isnull(', ' + State4, '')
from #tblTest
drop table #tblTest
January 20, 2006 at 9:16 am
Nicer & no trailing ',' - why did I go down the case route!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply