Query to concatenate results from multiple rows

I have a database where comments are stored in a separate table where the comment is split into max 80 char lengths and stored in separate rows.

eg.

RecordID Comment
001 This is a comment and the nex
001 t bit of the comment appears o
001 n the next line.
002 This is the start of the next com
002 ment.

I need a SQL query that will put the text back together again.

Many thanks
MUHow do you determine which order the segments should be assembled? Can they be put together in random order, or is there a definite sequence?

Do you want a solution that is simple, but SQL dialect specific, or do you want a generic solution that will work with most/all SQL dialects?

Do you want a solution for a single ID, or does it need to be able to work for the entire table in a single operation?

-PatP|||Pat,
Thanks for the response.

There is a LineNum field in the table to order the comments by.

The solution only needs to work with SQLServer.

Ideally I am looking for a solution that produces an entire set of rows showing details from a master table with the comment appearing from this table as a single field with the RecordID being used as the join field.

MarkU|||Ok, if you need to process multiple rows in a single set operation (ie SELECT statement), the best answer I've got is:CREATE TABLE #phrog (
recordId CHAR(3)
, comment VARCHAR(80)
, lineNum INT)

INSERT INTO #phrog (recordID, comment, lineNum)
SELECT '001', 'This is a comment and the nex', 1
UNION ALL SELECT '001', 't bit of the comment appears o', 2
UNION ALL SELECT '001', 'n the next line.', 3
UNION ALL SELECT '002', 'This is the start of the next com', 1
UNION ALL SELECT '002', 'ment.', 2

SELECT a.recordID, a.comment + Coalesce(b.comment, '') + Coalesce(c.comment, '')
FROM #phrog AS a
LEFT JOIN #phrog AS b
ON (b.recordID = a.recordID
AND b.lineNum = (SELECT Min(z1.lineNum)
FROM #phrog AS z1
WHERE z1.recordID = a.recordID
AND a.lineNum < z1.lineNum))
LEFT JOIN #phrog AS c
ON (c.recordID = a.recordID
AND c.lineNum = (SELECT Min(z1.lineNum)
FROM #phrog AS z1
WHERE z1.recordID = a.recordID
AND b.lineNum < z1.lineNum))
WHERE a.lineNum = (SELECT Min(z0.lineNum)
FROM #phrog AS z0
WHERE z0.recordID = a.recordID)

DROP TABLE #phrogBe forewarned that this code raises the kludge factor of the universe significantly, but it does work.

-PatP|||Many thanks for your help - I will check this out.

What I don't quite understand is that since I don't know upfront how many lines of comments there may be or what is in them, how can I do the UNION statements?

I was hoping that there would be some form of the UNION statement where I could say UNION ALL comment WHERE recordId = n (or similar).

MarkU|||On second thought, lets apply a very "Oracle-ish" solution. You could also use:CREATE TABLE dbo.phrog (
recordId CHAR(3)
, comment VARCHAR(80)
, lineNum INT)

INSERT INTO dbo.phrog (recordID, comment, lineNum)
SELECT '001', 'This is a comment and the nex', 1
UNION ALL SELECT '001', 't bit of the comment appears o', 2
UNION ALL SELECT '001', 'n the next line.', 3
UNION ALL SELECT '002', 'This is the start of the next com', 1
UNION ALL SELECT '002', 'ment.', 2
GO

CREATE FUNCTION dbo.phrogComment(@.recordID CHAR(3))
RETURNS VARCHAR(8000) AS
BEGIN
DECLARE
@.c VARCHAR(8000)
, @.r VARCHAR(8000)

SET @.r = ''

DECLARE z CURSOR FOR SELECT
comment
FROM dbo.phrog
WHERE recordID = @.recordID
ORDER BY lineNum

OPEN z
FETCH z INTO @.c

WHILE 0 = @.@.fetch_status
BEGIN
SET @.r = @.r + @.c
FETCH z INTO @.c
END

CLOSE z
DEALLOCATE z

RETURN @.r
END
GO

SELECT a.recordID, dbo.PhrogComment(a.recordID)
FROM dbo.phrog AS a
GROUP BY a.recordID

DROP FUNCTION dbo.phrogComment
DROP TABLE dbo.phrogThis will grieviously disturb the relational purist (me included), but it will get the job done quickly and simply.

-PatP|||I tried the second bit of code on my own tables, and it almost works perfectly. The problem I have is that the concatenated field being returned is being truncated at 256 total characters/spaces, yet I need it to be larger.

I tried to use a CAST on the PhrogComment(a.ID), as well as changing the VARCHAR sizes for @.c and @.r and the RETURNS value, all to no avail.

Any suggestions on how I could tweak the code to make the result "larger"?

Thanks,

Mark|||'taint the SQL code what's cuttin' ya off. It's the client.

In Query Analyzer:

1) Press shift-control-o to bring up the Options window.
2) Click the results tab.
3) At the right edge, near the middle, type in whatever column width seems kozy but not extravagant.
4) Re-run your query for optimum viewing pleasure!

Sorry if I'm a bit punchy... Things could charitably be described as "interesting" today.

-PatP|||Praise God! I've been losing my mind for the last 24 hours (it's been - how did you say it? - "interesting" :-)

Thanks so much. I should've known to blame it on SQL Query Analyzer - I've had some queries not work (i.e., a query will return 0 rows and throw no errors) in the Analyzer yet the same query works (return the expected results) if cut and pasted into and then run as a stored procedure - go figure.

Then again, I'm an econ major so the problem is probably behind the keyboard...

Mark|||Are you just wanting to do this for one message at a time in your procedure or are you wanting to return several messages.