April 12, 2007 at 7:06 am
Hi guys,
I need to retrieve a comma separated values from the database.
i.e I have a linking table that contains a Branch To Location association, by associating a BranchID to a LocalityID.
My Query Looks like this
Select dbo.LOCALITY.LOCALITY from dbo.LOCALITY
inner join dbo.BranchLocality on dbo.BranchLocality.LOCALITY_ID = dbo.LOCALITY.ID
where dbo.BranchLocality.BRANCH_ID = 34
This returns a Table.
i.e
New York
Alaska
California
Wyoming
Utah
What I would Like to achieve is that I return it as one field with a a Comma Separating the values
i.e
"New York, Alaska, California, Wyoming, Utah" etc
Kind Regards,
Gary
Gary Woodfine
threenineconsulting.com
April 12, 2007 at 7:51 am
DECLARE @Location VARCHAR(2000)
SELECT @Location = @Location + dbo.LOCALITY.LOCALITY + ','
FROM dbo.LOCALITY
INNER JOIN dbo.BranchLocality
ON dbo.BranchLocality.LOCALITY_ID = dbo.LOCALITY.ID
where dbo.BranchLocality.BRANCH_ID = 34
SET @Location = SUBSTRING(@Location, 1, LEN(@Location) - 1)
SELECT @Location
@Location should look liked "New York, Alaska,...."
April 12, 2007 at 7:56 am
Using the above, I would make one change (you actually have two choices for this one change):
DECLARE @Location VARCHAR(2000)
set @Location = '' -- this
SELECT @Location = @Location + dbo.LOCALITY.LOCALITY + ','
-- SELECT @Location = isnull(@Location,'') = dbo.LOCALITY.LOCALITY + ',' -- or this
FROM dbo.LOCALITY
INNER JOIN dbo.BranchLocality
ON dbo.BranchLocality.LOCALITY_ID = dbo.LOCALITY.ID
where dbo.BranchLocality.BRANCH_ID = 34
SET @Location = SUBSTRING(@Location, 1, LEN(@Location) - 1)
SELECT @Location
@Location should look liked "New York, Alaska,...."
April 12, 2007 at 8:02 am
April 12, 2007 at 4:40 pm
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply