Can I merge all rows into a songle row?

Frunkie · #1 (**permalink**) 08-17-09, 08:59

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

aflorin27 · #2 (**permalink**) 08-17-09, 09:06

try SELECT DISTINCT

r937 · #3 (**permalink**) 08-17-09, 09:08

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

r937 · #4 (**permalink**) 08-17-09, 09:09

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

Frunkie · #5 (**permalink**) 08-17-09, 09:24

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!

Frunkie · #6 (**permalink**) 08-17-09, 09:54

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

r937 · #7 (**permalink**) 08-17-09, 10:53

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

Frunkie · #8 (**permalink**) 08-17-09, 16:42

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.

Frunkie · #9 (**permalink**) 08-17-09, 17:39

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:

Quote:

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.

r937 · #10 (**permalink**) 08-17-09, 19:18

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?

Frunkie · #11 (**permalink**) 08-17-09, 19:27

Ok Rudy, sorry for the confusion. I'll start a new thread now.

Internet Services

Web Development

Digital Marketing

Consumer Tech