Query to concatenate results from multiple rows

underhillm · #1 (**permalink**) 04-05-04, 11:04

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
MU

Pat Phelan · #2 (**permalink**) 04-05-04, 12:02

How 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

underhillm · #3 (**permalink**) 04-05-04, 13:03

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

Pat Phelan · #4 (**permalink**) 04-05-04, 13:42

Ok, if you need to process multiple rows in a single set operation (ie SELECT statement), the best answer I've got is:

PHP Code:


			
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 #phrog

Be forewarned that this code raises the kludge factor of the universe significantly, but it does work.

-PatP

underhillm · #5 (**permalink**) 04-05-04, 14:16

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

Pat Phelan · #6 (**permalink**) 04-05-04, 15:24

On second thought, lets apply a very "Oracle-ish" solution. You could also use:

PHP Code:


			
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.phrog

This will grieviously disturb the relational purist (me included), but it will get the job done quickly and simply.

-PatP

markchristenson · #7 (**permalink**) 05-18-04, 17:08

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

Pat Phelan · #8 (**permalink**) 05-18-04, 17:25

'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

markchristenson · #9 (**permalink**) 05-18-04, 18:10

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

derrickleggett · #10 (**permalink**) 05-18-04, 19:52

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

Internet Services

Web Development

Digital Marketing

Consumer Tech