Set based solution possible? - SQL Server Forums

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)

Set based solution possible?

Author

Topic

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2004-01-16 : 09:08:38

Greetings...


create table test (theKey int, line_number int, theText varchar(100))

insert into test(theKey, line_number, theText)
select 4, 1, 'I work with SQL Server' union all
select 4, 2, 'but I am not, I repeat' union all
select 4, 3, 'I am NOT' union all
select 4, 4, 'a member of the Microsoft fanboy club! NEVER!' union all
select 5, 1, 'Want to listen to some' union all
select 5, 2, 'cool music then get yourself' union all
select 5, 3, 'Chimera by Delerium, it is worth the mney!'

What I am after is to combine the line(s) where the key is the same to ONE line. Any ideas on how to do this with a set based solution?

Thanks in advance.

________________
Make love not war!

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-01-16 : 09:20:35

I guess you'll be pointed to here......http://www.sqlteam.com/item.asp?ItemID=11021

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2004-01-16 : 09:34:27

Ya, I was gonna do that

, but I thought I'd write some code first.
Based of Byrmol's UDF in the article comments.

create function getLine(@i int) returns varchar(8000)
as
begin
declare @String varchar(8000)
Select @String = theText + ' ' + isNull(@String, '') from test where theKey = @i Order by line_number desc
return left(@String, len(@String)-1)
end
go

Select theKey, dbo.getLine(theKey) as line
from test
group by theKey

Damian

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2004-01-16 : 11:44:31

Thanks to you both.

________________
Make love not war!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources