Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Apr 2004
    Posts
    4

    Question Unanswered: How to Combine results from multiple records into one

    Hello,

    I have a table which has the following structure:

    ID MessageText
    001 Hello
    001 There
    001 Working
    003 See
    003 you
    003 Next
    003 Time

    How to build a query or store procedure to return result like this:

    ID MessageText
    001 Hello There Working
    003 See you Next Time

    Your help/advice is greatly appreciated.

    Thanks, Ficisa

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You don't have anything that would identify the order of the words in the sentence.....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Apr 2004
    Posts
    4
    The order is not important, as long as I can put them together into one field.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    USE Northwind
    GO
    
    CREATE TABLE myTable99(WordOrder int IDENTITY(1,1), [ID] char(3), MessageText varchar(255))
    GO
    
    INSERT INTO myTable99([ID], MessageText)
    SELECT '001', 'Hello'   UNION ALL
    SELECT '001', 'There'   UNION ALL
    SELECT '001', 'Working' UNION ALL
    SELECT '003', 'See'     UNION ALL
    SELECT '003', 'you'     UNION ALL
    SELECT '003', 'Next'    UNION ALL
    SELECT '003', 'Time'
    GO
    
    DECLARE @messagetext varchar(2000), @MAX_ID char(3), @MIN_ID char(3)
    
    DECLARE @myTable99 TABLE ([ID] char(3), messagetext varchar(2000))
    
    SELECT    @MAX_ID = MAX([ID]) 
    	, @MIN_ID = MIN([ID])
      FROM	  myTable99
    
    SELECT @messagetext = ''
    
    WHILE @MIN_ID <> @MAX_ID
      BEGIN
    	SELECT @messagetext = COALESCE(@messagetext+ ' ','') + MessageText
    	  FROM myTable99
    	 WHERE [ID] = @MAX_ID
      
    	INSERT INTO @myTable99([ID], MessageText)
    	SELECT @MAX_ID, @MessageText
    
    	SELECT    @MAX_ID = MAX([ID])
    	  FROM	  myTable99
    	 WHERE	  [ID] < @MAX_ID
    
    	SELECT @messagetext = ''
      END
    
    SELECT @messagetext = COALESCE(@messagetext+ ' ','') + MessageText
      FROM myTable99
     WHERE [ID] = @MIN_ID
    
    INSERT INTO @myTable99([ID], MessageText)
    SELECT @MIN_ID, @MessageText
    
    SELECT * FROM @myTable99
    GO
    
    DROP TABLE myTable99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    aaaaaaaaaaaarggggh! code!!!

    my eyes!!!!! they're BLEEEEEEEEEDING!!


    hey, how's this for a solution:

    PHP Code:
    select ID
         
    group_concat(MessageText) as MessageText
      from atable
    group
        by ID 
    see GROUP BY functions

    not sql server?

    pity, eh

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    That's mySQL not SQL Server
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Ok, how about the hybred approach:
    PHP Code:
    CREATE TABLE dbo.tMessages (
       
    Id        CHAR(3)        NOT NULL
    ,  MessageText    VARCHAR(20)    NOT NULL
      
    )

    INSERT dbo.tMessages (IdMessageText)
       
    SELECT           '001''Hello'
       
    UNION ALL SELECT '001''There'
       
    UNION ALL SELECT '001''Working'
       
    UNION ALL SELECT '003''See'
       
    UNION ALL SELECT '003''you'
       
    UNION ALL SELECT '003''Next'
       
    UNION ALL SELECT '003''Time'

    DROP FUNCTION dbo.fGrunge
    GO
    CREATE 
    FUNCTION dbo.fGrunge (
       @
    id        CHAR(3)
       ) 
    RETURNS VARCHAR(4000) AS
    BEGIN
       
    DECLARE @c VARCHAR(20), @r VARCHAR(8000)
       
    SET @''

       
    DECLARE zfGrunge CURSOR FOR SELECT MessageText
       FROM dbo
    .tMessages
       WHERE  id 
    = @id
       ORDER BY MessageText

       OPEN zfGrunge
       FETCH zfGrunge INTO 
    @c

       
    WHILE = @@fetch_status
          BEGIN
             SET 
    @= @' ' + @c
             FETCH zfGrunge INTO 
    @c
          END

       CLOSE zfGrunge
       DEALLOCATE zfGrunge

       
    RETURN SubString(@r28000)
    END
    GO

    SELECT a
    .iddbo.fGrunge(a.id)
       
    FROM dbo.tMessages AS a
       GROUP BY a
    .id
       ORDER BY a
    .id

    DROP TABLE dbo
    .tMessages 
    -PatP

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Pat,

    A cursor?

    Damn, it's only Monday.....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Apr 2004
    Posts
    4
    Oh wow,

    Perfect!!!

    Brett's answer works great. There sure are some programming, it would be nice to have group_concat() function in MS SQL. Forgot I can create one of my own. Thanks very much Pat!!!

    Now here's another question, I was trying to use cursor to solve it, like in Pat's function, does it have any disadvantage/advantage over Brett's solution?

    Thank you a bunch!!!

    ficisa

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I can do it with oodles of left joins, but that gets ugly too. While cursors are a good way to kill an application, they are intent on killing this one anyway.

    Trawling the result set is better done on the middle tier or the client anyway. This is fundamentally poor design. I was just offering this as a solution to the stated problem, not necessarily advocating it!

    -PatP

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    How much data you talking about?

    Why not perf bechmark both and let us know...(ya gotta do something... )

    But I'm thinking (ok, ok, don't all be sooo amazed) that the cursor will be slower...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Originally posted by Brett Kaiser
    That's mySQL not SQL Server
    dude, i knew that

    (something about grandmothers and eggs)


    that's why i posted it!!

    so that you sql server guys could SEE HOW IT SHOULD BE DONE

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by r937
    dude, i knew that

    (something about grandmothers and eggs)


    that's why i posted it!!

    so that you sql server guys could SEE HOW IT SHOULD BE DONE

    Rudy, where is that in the ANSI guide?

    I can't find it....

    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ansi?

    shirley you jest

    as if microsoftborg gave two figs for ansi
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Don't call me shirley....

    I prefer laverne
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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