Concatinate row values in a column
-
DECLARE @lTable TABLE (LastName varchar(100), City varchar(100))
insert into @lTable values ('Gates', 'Chicago')
insert into @lTable values ('Woods', 'Chicago')
insert into @lTable values ('Winslow', 'New York')
insert into @lTable values ('Rock', 'Dallas')
insert into @lTable values ('Giant', 'Las Vegas')
insert into @lTable values ('Woods', 'Dallas')
insert into @lTable values ('Gates', 'Miami')
insert into @lTable values ('Woods', 'Los Angeles')
Here is what I am trying to do. I need to concatinate the city for the same LastName.
Result Set:
Woods -- Chicago, Dallas, Los Angeles
Gates -- Chicago, Miami
Winslow -- New York
Rock -- Dallas
Is it possible in a single query?
Thanks.
-R
-
I did this with a Function, so it needed a physical table for lTable.
CREATE TABLE dbo.lTable( LastName varchar(100), City varchar(100))
INSERT INTO lTable VALUES( 'Gates', 'Chicago')
INSERT INTO lTable VALUES( 'Woods', 'Chicago')
INSERT INTO lTable VALUES( 'Winslow', 'New York')
INSERT INTO lTable VALUES( 'Rock', 'Dallas')
INSERT INTO lTable VALUES( 'Giant', 'Las Vegas')
INSERT INTO lTable VALUES( 'Woods', 'Dallas')
INSERT INTO lTable VALUES( 'Gates', 'Miami')
INSERT INTO lTable VALUES( 'Woods', 'Los Angeles')
GO
-------------------------------------------------------------------------------------------
CREATE FUNCTION dbo.GetCities( @LastName varchar(100))
RETURNS varchar(7900)
AS
BEGIN
DECLARE @Cities varchar(7900)
SET @Cities = ''
SELECT @Cities = CASE
WHEN @Cities = ''
THEN RTRIM( City)
ELSE @Cities + ', ' + RTRIM( City)
END
FROM dbo.lTable
WHERE LastName = @LastName
ORDER BY City
RETURN( @Cities)
END
GO
-------------------------------------------------------------------------------------------
DECLARE @Output TABLE( LastName varchar(100), Cities varchar(7900))
INSERT INTO @Output( LastName) SELECT DISTINCT LastName FROM dbo.lTable
UPDATE @Output SET
Cities = dbo.GetCities( LastName)
SELECT LastName + ' -- ' + Cities FROM @Output
DROP FUNCTION dbo.GetCities
DROP TABLE dbo.lTable
I wasn't born stupid - I had to study.
-
Thanks Farrell for the solution. But is there a way to do this in a single query.
-
Sure. Just drop the @OutPut table, (I should have done that from the beginning):
CREATE TABLE dbo.lTable( LastName varchar(100), City varchar(100))
INSERT INTO lTable VALUES( 'Gates', 'Chicago')
INSERT INTO lTable VALUES( 'Woods', 'Chicago')
INSERT INTO lTable VALUES( 'Winslow', 'New York')
INSERT INTO lTable VALUES( 'Rock', 'Dallas')
INSERT INTO lTable VALUES( 'Giant', 'Las Vegas')
INSERT INTO lTable VALUES( 'Woods', 'Dallas')
INSERT INTO lTable VALUES( 'Gates', 'Miami')
INSERT INTO lTable VALUES( 'Woods', 'Los Angeles')
GO
-------------------------------------------------------------------------------------------
CREATE FUNCTION dbo.GetCities( @LastName varchar(100))
RETURNS varchar(7900)
AS
BEGIN
DECLARE @Cities varchar(7900)
SET @Cities = ''
SELECT @Cities = CASE
WHEN @Cities = ''
THEN RTRIM( City)
ELSE @Cities + ', ' + RTRIM( City)
END
FROM dbo.lTable
WHERE LastName = @LastName
ORDER BY City
RETURN( @Cities)
END
GO
-------------------------------------------------------------------------------------------
SELECT DISTINCT LastName + ' -- ' + dbo.GetCities( LastName) FROM lTable
DROP FUNCTION dbo.GetCities
DROP TABLE dbo.lTable
I wasn't born stupid - I had to study.
-
Thanks much Farrel, that worked great for me!
-
Glad I could help. Thanks for replying from another post!
I wasn't born stupid - I had to study.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply