String Concat in Group
-
I have two tables, say table A & B:
A.ID A.Name
------------------
1 AAA
2 BBB
3 CCC
B.RowID B.FID B.Value
---------------------------
1 1 111
2 1 222
3 2 333
4 3 444
5 3 555
6 3 666
Now, how can I get a resultset as:
Name Value
----------------
AAA 111,222
BBB 333
CCC 444,555,666
Thanks for your help.
-
select a.id
, b.value
, c.value
from a
inner join b on a.id = b.fid
left outer join b as c on c.fid = a.id
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
-
I have to do this often for reporting purposes; it ain't easy, but this is relatively effecient (even though it uses both cursors and temp tables -- YUK).
--Example creation and insertion
CREATE TABLE A ([ID] int, [NAME] varchar(3))
CREATE TABLE B ([RowID] int, [FID] int, [NAME] varchar(3))
INSERT INTO [A] VALUES (1, 'AAA')
INSERT INTO [A] VALUES (2, 'BBB')
INSERT INTO [A] VALUES (3, 'CCC')
INSERT INTO VALUES (1, 1, '111')
INSERT INTO VALUES (2, 1 ,'222')
INSERT INTO VALUES (3, 2, '333')
INSERT INTO VALUES (4, 3 ,'444')
INSERT INTO VALUES (5, 3 ,'555')
INSERT INTO VALUES (6, 3, '666')
SELECT *
FROM A
SELECT *
FROM B
--Create Outer Cursor for concatenation
DECLARE @AID int
DECLARE @Value varchar(3)
DECLARE @Concat varchar(50)
CREATE TABLE #Output
(AID varchar(3),
[Value] varchar(50))
DECLARE C1 CURSOR
FOR SELECT ID
FROM A
OPEN C1
FETCH NEXT FROM C1
INTO @AID
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Concat = '' --empty out the concatenation string
--DECLARE INNER LOOP For Concatenate
DECLARE C2 Cursor
FORSELECT [NAME]
FROM B
WHERE FID = @AID
OPEN C2
FETCH NEXT FROM C2
INTO @Value
WHILE @@FETCH_STATUS = 0
BEGIN
--for everry record, insert the value and concatenate
SET @Concat = @Concat + @Value + ','
FETCH NEXT FROM C2
INTO @Value
END
--inner loop is done, so insert into temp table and move to the next record
INSERT INTO #Output
VALUES (@AID , @Concat)
CLOSE C2
DEALLOCATE C2
FETCH NEXT FROM C1
INTO @AID
END
CLOSE C1
DEALLOCATE C1
--output the data
SELECT A.NAME, LEFT(#Output.Value, LEN(#Output.Value)-1) AS Value
FROM A JOIN #output ON A.ID = #Output.AID
DROP TABLE #Output
--Cleanup
DROP TABLE A
DROP TABLE B
-
Just an option- doesn't do anything more than what sainswor99's code did tho:
after table creation and fill run
declare @x table (
IDCol int identity(1,1) primary key -- provides clustered index
,RowID int not null
,FKID int not null
,TextField varchar(3) not null
,ExtendedTextField varchar(255) not null
)
insert into @x (RowID, FKID, TextField, ExtendedTextField)
select RowID, FID, [Name], '' from b order by FID, [Name]
declare @ExtendedText varchar(255), @LastFKID int
set @LastFKID = -1
set @ExtendedText = ''
update @x
set
@ExtendedText = ExtendedTextField = convert(varchar(255), case when @LastFKID = FKID then @ExtendedText+',' else '' end +TextField )
,@LastFKID = FKID
select * from @x
-
-- The "Inner" cursor could be replaced with...
-- .....
SET @Concat = '' --empty out the concatenation string
--DECLARE INNER LOOP For Concatenate
SELECT @Concat = @Concat + Case When @Concat = '' Then '' Else ',' End + [NAME]
FROM B
WHERE FID = @AID
--inner loop is done, so insert into temp table and move to the next record
-- ......
--output the data (Changed because of "comma" work above)
--SELECT A.NAME, LEFT(#Output.Value, LEN(#Output.Value)-1) AS Value
SELECT A.NAME, #Output.Value AS Value
FROM A
JOIN #Output ON A.ID = #Output.AID
Once you understand the BITs, all the pieces come together
-
--Jonathan
Edited by - Jonathan on 11/21/2003 2:39:50 PM
--Jonathan -
CREATE FUNCTION dbo.ListB(@Fid int)
RETURNS varchar(8000) BEGIN
DECLARE @List varchar(8000)
SELECT @List = ISNULL(@List + ',','') + Value
FROM B
WHERE FID = @Fid
ORDER BY RowID -- or Value? Can't tell from your post...
RETURN @List END
SELECT DISTINCT Name, dbo.ListB(ID) Values
FROM A
ORDER BY Name--Jonathan
--Jonathan -
Thank you all for your response. You've been of great help.
Jonathan: your solution works well and its the cleanest as well. Thanks!
-
🙂 Thank you for this answer. Helped me greatly.
-
steve.spenceST (3/20/2014)
🙂 Thank you for this answer. Helped me greatly.Note that there are better ways of doing this. As explained in here: http://www.sqlservercentral.com/articles/comma+separated+list/71700/
-
Luis Cazares (3/20/2014)
steve.spenceST (3/20/2014)
🙂 Thank you for this answer. Helped me greatly.Note that there are better ways of doing this. As explained in here: http://www.sqlservercentral.com/articles/comma+separated+list/71700/
Ditto that!
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
steve.spenceST (3/20/2014)
🙂 Thank you for this answer. Helped me greatly.If we consider the original post, THIS is what Luis is talking about...
--===== Create the test tables with the OP data.
-- This is NOT a part of the solution.
SELECT a.ID, a.Name
INTO #TableA
FROM (
SELECT 1,'AAA' UNION ALL
SELECT 2,'BBB' UNION ALL
SELECT 3,'CCC'
) a (ID,[Name])
;
SELECT b.RowID, b.FID, b.Value
INTO #TableB
FROM (
SELECT 1,1,111 UNION ALL
SELECT 2,1,222 UNION ALL
SELECT 3,2,333 UNION ALL
SELECT 4,3,444 UNION ALL
SELECT 5,3,555 UNION ALL
SELECT 6,3,666
) b (RowID, FID, [Value])
;
--===== Solve the solution using the concatenation abilities of FOR XML PATH.
SELECT a.Name
,[Value] = STUFF(
(SELECT ',' + CAST(b.Value AS VARCHAR(10))
FROM #TableB b
WHERE b.FID = a.ID
ORDER BY b.Fid
FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
,1,1,'')
FROM #TableA a
;
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply