Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2004
    Posts
    3

    Question Unanswered: 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
    MU

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  3. #3
    Join Date
    Apr 2004
    Posts
    3
    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

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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
    .recordIDa.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

  5. #5
    Join Date
    Apr 2004
    Posts
    3
    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

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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 (recordIDcommentlineNum)
       
    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 @''

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

          OPEN z
          FETCH z INTO 
    @c

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

          CLOSE z
          DEALLOCATE z

          
    RETURN @r
       END
    GO

    SELECT a
    .recordIDdbo.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

  7. #7
    Join Date
    May 2004
    Posts
    2
    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

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    '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

  9. #9
    Join Date
    May 2004
    Posts
    2
    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

  10. #10
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    Are you just wanting to do this for one message at a time in your procedure or are you wanting to return several messages.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •