Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Concatenate from @TableVar
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Kristen
Test

United Kingdom
22858 Posts

Posted - 11/07/2007 :  12:50:36  Show Profile  Reply with Quote

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'

I want to concatenate matching LinkID (in ID order) to form:

1, 'Foo,Bar'
2, 'Xxx,Yyy'
3, 'Aaa'
4, 'Bbb'

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
Flowing Fount of Yak Knowledge

USA
2507 Posts

Posted - 11/07/2007 :  13:15:56  Show Profile  Visit dinakar's Homepage  Reply with Quote
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)

Singapore
17689 Posts

Posted - 11/07/2007 :  13:18:00  Show Profile  Reply with Quote
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
Time is always against us

Go to Top of Page

Kristen
Test

United Kingdom
22858 Posts

Posted - 11/08/2007 :  01:05:28  Show Profile  Reply with Quote
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

Edited by - Kristen on 11/08/2007 01:05:48
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000