Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803

    Unanswered: Can I merge all rows into a songle row?

    I will always have 2 rows of data; each row differs only by a single column that I am using as a flag. What I need is to somehow concatenate or merge the results into a single row. Im also using a couple of inner joins to get the results. Could someone please point me to an example of what I need?

    Thanks,
    Frank

  2. #2
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    try SELECT DISTINCT
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Frunkie
    Could someone please point me to an example of what I need?
    sure
    Code:
    SELECT gromulphastic.scrimflabat
         , gromulphastic.opsilfrummer
         , gromulphastic.clapfrepple
         , GROUP_CONCAT(haplistplogget.cramdibulort) AS cramdibulorts
      FROM frabilgimjer
    INNER
      JOIN quistipunctous
        ON quistipunctous.scrimflabat = frabilgimjer.horbliston
    INNER
      JOIN haplistplogget
        ON haplistplogget.opsilfrummer = quistipunctous.whipfintaggle
    INNER
      JOIN gromulphastic
        ON gromulphastic.corbustackle = quistipunctous.clapfrepple
     WHERE frabilgimjer.fuhfuhid = 'Frunkie'
    GROUP
        BY gromulphastic.scrimflabat
         , gromulphastic.opsilfrummer
         , gromulphastic.clapfrepple
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by aflorin27
    try SELECT DISTINCT
    nope, that won't do it

    he distinctly said "each row differs" and he doesn't want them separate, he wants them combined
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Heya Rudy!

    Thanks. Another sleepless night because I don't know when to leave well enough alone and go to bed.

    I was posting back to tell aflorin that DISTINCT wasn't going to work. What I needed was a simple GROUP BY as you have in your example.

    Talk about not seeing da forest for da trees.

    Thanks again Rudy!

  6. #6
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Rudy, while I'm thinking about it, let me ask you.. I have 3 seperate queries for different events / actions. They are are all identical with the exception of a single value in the WHERE clause which is used as a flag. I'm just wondering if there would be a way, or some SQL statement like CASE where I can consolidate these 3 queries into one. I hope that makes sense.

    Thanks

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Frunkie
    I'm just wondering if there would be a way, or some SQL statement like CASE where I can consolidate these 3 queries into one.
    yes, there is a way
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    The suspense is killing me now. Is it CASE that I need to do this?

    EDIT:
    I'll hold off on this for a while.. I've got bigger fish to fry right now.
    Last edited by Frunkie; 08-17-09 at 18:40.

  9. #9
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    I have some issues still going on with the example you provided. I used the GROUP_CONCAT() function and it works well but it puts both values into a single cell. What I was hopng for was for each value to have its own cell. Is there a way to do this?

    This is the query:
    Code:
    SELECT
      t2.msg_seq
      , t3.lname
      , t1.subject
      , t1.message
      , stamp
    FROM
      mail_message AS t1
        Inner Join mailbox AS t2
          ON t1.msg_seq = t2.msg_seq
        Inner Join employee AS t3
          ON t3.employee_id = t2.employee_id
    That DDL produces this:
    1 Administrator Test to number 2 hello 2. 2009-08-17 03:57:35
    1 Miller Test to number 2 hello 2. 2009-08-17 03:57:35
    What I need to have are both the sender and recipient's last name on a single row.
    Last edited by Frunkie; 08-17-09 at 18:43.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    frank, i'm going to take a moment and chide you gently for taking me down the garden path

    your first post was mysterious, and shrouded in generalities -- you wanted to "somehow concatenate or merge the results into a single row"

    GROUP_CONCAT certainly does that, so i gave you a general answer, with nonsense table/column names, just so that you'de have something to see (unlike i did)

    then you asked another over-simplified question, and the best i could do was answer in the affirmative, as there were no details whatsoever to go on

    now all of a sudden i am asked to understand sender and recipient for three different tables i've never seen before, in a query of unknown merit...

    frank, you gots to learn to ask better questions

    many of us here at dbforums, myself included, enjoy helping people -- for free, i should point out -- but sometimes the shenanigans are a bit over the top, ya know?

    why don't you start a new thread for your sender/recipient issue, give complete table layout details and sample data, and be more specific about what you're doing

    and from now on, please don't over-simplify, okay?

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

  11. #11
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Ok Rudy, sorry for the confusion. I'll start a new thread now.

Posting Permissions

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