SQL Server Forums
Profile | Register | 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
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Kristen
Test

United Kingdom
22415 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
17642 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
22415 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  
 New 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.06 seconds. Powered By: Snitz Forums 2000