how to get multiple records into one

[mwa]

I have a table that has the following layout:
req line comment
12345 1 This field has
12345 2 some text in
12345 3 it that should
12345 4 all be together.

Each req will have 1 or more lines with pieces of the whole comment. Is there a way to write a select statement that will return the multiple records into 1 record like:

req comment
12345 This field has some text in it that should all be together.

I hope this makes sense. Thanks for the help...





mwa
<><

 

[SQLSister]

If I take you correctly the number of lines may differ from record to record?

This is a poor design and I would change it if at all possible. There is no efficient way to extract this data in the form you need it, so redeisign is by far your best choice.

That said the method to extract the data you currently have would be along the lines of looping through the records and then copying the comment field and RecordID to a temp table (or preferably to your redesigned table) where if the id exists it is concatentated to the existing data (make sure to add appropraite spaces) or if the id does not exist you would insert the record. Since this is very inefficent, if you will have a large number of records in the system and this select will be called frequently, a redesign will be necessaary not just optional.

YOu'll need to look up cursors, temp tables and concatenation to figure out how to do this.

 

[mwa]

My thoughts exactly on the poor design. Unfortunately, I can not redesign the tables. This data is part of the back end for a third party financial system that my company purchased a few years ago. I have been tasked with writing some reports based on this data. I am adept with cursors, temp tables, concatenation, etc... I was just hoping to avoid this because of the inefficency, like you mentioned...

Thanks...

mwa
<><

 

[ClaireHCM]

Not that bad dude!

create function dbo.udf_Comment (@req int)
Returns varchar(2000)
Begin
declare @com varchar(2000)
set @com = ''
select @com = @com+comment+' ' from TBName where req = @req
Return @com
End

--Usage
--select dbo.udf_comment(req) from TBName group by req

 

[mwa]

You had me excited for a second... Then I remembered that we are on SQL 7.0 :-(

Maybe it is that bad!! Any other ideas?

mwa
<><

 

[ClaireHCM]

Oops...pretty bad...I would go for cursor or temp table.Because sometimes not always u can find the efficient way unless your dbms allows u to do so.

 

[mwa]

I ended up writing a cursor to loop through each record. It's running pretty smooth but, I know it is inefficient.

Thanks for your help again...

mwa
<><

 

[SleepingSand]

Use it like it...

DECLARE @mComment varchar(500)
SELECT @mComment = COALESCE(@mComment, ' ') + Comment
FROM Table1
SELECT DISTINCT req, @mComment
FROM Table1

This should work... Please check out.

 

[mwa]

This is almost working... If I add a where clause to what you have (where req = '12345'), then it will work. But if I do not add that where clause, then it seems to append all of the comments together, regardless of the req number. Any other ideas on how to tweak this?

mwa
<><

 

[kshanes]

SleepingSand,

I too have a similar problem as mwa. Your suggestion works great if there is no need for a where clause. I've tried different things to get your solution to work with a where clause without success. Do you happen to know how to get it to work with a WHERE clause? Thanks in advance for any additional direction you can provide.

 

[mwa]

Weird, I was only able to get this to work using a where clause. Make sure that you enter the where on both select statements:

DECLARE @mComment varchar(500)
SELECT @mComment = COALESCE(@mComment, ' ') + Comment
FROM Table1
[blue]WHERE req = '12345'[/blue]
SELECT DISTINCT req, @mComment
FROM Table1
[blue]WHERE req = '12345'[/blue]


mwa
<><