Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
Kristen
Test
22859 Posts |
Posted - 2007-11-07 : 12:50:36
|
[code]DECLARE @TestData TABLE( ID int IDENTITY(1,1) NOT NULL, LinkID int NOT NULL, Name varchar(10) NOT NULL)INSERT INTO @TestData(LinkID, Name)SELECT 1, 'Foo' UNION ALLSELECT 1, 'Bar' UNION ALLSELECT 2, 'Xxx' UNION ALLSELECT 2, 'Yyy' UNION ALLSELECT 3, 'Aaa' UNION ALLSELECT 4, 'Bbb'[/code]I want to concatenate matching LinkID (in ID order) to form:[code]1, 'Foo,Bar'2, 'Xxx,Yyy'3, 'Aaa'4, 'Bbb'[/code]The data is in an @TableVar in an Sproc. I would prefer NOT to materialise it into a permanent table, but if I did I would know how to use a UDF to get this from it. SQL 2000 server (no SQL 2005 available for this job)ThanksKristen |
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-11-07 : 13:15:56
|
I am thinking you will have to loop through...DECLARE @TestData TABLE( ID int IDENTITY(1,1) NOT NULL, LinkID int NOT NULL, Name varchar(10) NOT NULL)INSERT INTO @TestData(LinkID, Name)SELECT 1, 'Foo' UNION ALLSELECT 1, 'Bar' UNION ALLSELECT 2, 'Xxx' UNION ALLSELECT 2, 'Yyy' UNION ALLSELECT 3, 'Aaa' UNION ALLSELECT 4, 'Bbb'Select * from @TestData Declare @TestData2 table ( LinkID int NOT NULL, ConcatName varchar(100), Processed int)Insert into @TestData2 (Linkid, concatname, Processed ) Select distinct LinkId, NULL, 0FROM @TestData Declare @val varchar(50), @Linkid intWhile exists (Select * from @TestData2 Where processed = 0) Begin Select @Linkid = MIN(LinkId) FROM @TestData2 Where processed = 0 Set @val = null Select @val = COALESCE(@val+', ', '') + T.Name FROM @TestData T WHERE T.LinkID = @Linkid Update T2 Set T2.ConcatName = @val From @TestData2 T2 Where T2.Linkid = @Linkid update @TestData2 Set Processed = 1 WHERE Linkid = @Linkid Endselect * from @TestData2 Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-11-07 : 13:18:00
|
if is it max 2 Name per LinkID SELECT LinkID, CASE WHEN MIN(Name) <> MAX(Name) THEN MIN(Name) + ',' + MAX(Name) ELSE MIN(Name) ENDFROM @TestDataGROUP BY LinkID KH[spoiler]Time is always against us[/spoiler] |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-11-08 : 01:05:28
|
Can I use AVERAGE to get a third? "I am thinking you will have to loop through..."OK, well if no brain-waves that's what it will be.I was thinking of updating @TestData2 with the MIN(ID) for each LinkID, and then concatenate [one more] name for that, then move on to the next MIN(ID) > Current.So it will only loop for the max. number of concatenated names in a single row, rather than once-per-rowKristen |
|
|
|
|
|
|
|