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.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Concatenate from @TableVar

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 ALL
SELECT 1, 'Bar' UNION ALL
SELECT 2, 'Xxx' UNION ALL
SELECT 2, 'Yyy' UNION ALL
SELECT 3, 'Aaa' UNION ALL
SELECT 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)

Thanks

Kristen

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 ALL
SELECT 1, 'Bar' UNION ALL
SELECT 2, 'Xxx' UNION ALL
SELECT 2, 'Yyy' UNION ALL
SELECT 3, 'Aaa' UNION ALL
SELECT 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, 0
FROM @TestData


Declare @val varchar(50), @Linkid int
While 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
End

select * from @TestData2





Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Go to Top of Page

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) END
FROM @TestData
GROUP BY LinkID



KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

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-row

Kristen
Go to Top of Page
   

- Advertisement -