Combine 2 rows to a single one. 1

[Sleidia]

Hello.

Another headache for me today, plus a documentation that doesn't want to show me what I need to know.

Here is the problem:

I have a table that looks like this:

ID_1 | ID_2 | ST_1 | ST_2 |
1    |1     |A     |      |
2    |1     |      |B     |
3    |1     |C     |      |
4    |2     |B     |      |

I would like to select A and B where ID_2 has 1, but I absolutely need to output only one row per ID_2 ( GROUP BY clause ).
output-> |1 |A |B |

Is it possible?

The only solution I've found is to change the data to:

ID_1 | ID_2 | ST_1 | ST_2 |
1    |1     |A     |B     |
2    |1     |A     |B     |
3    |1     |C     |C     |
4    |2     |B     |B     |

... like I did for ID_2, but I don't like it this way.

Many thanks to the one who will take time to share his point of view on that problem.
Have a nice day.

 

[shadedecho]

In your scenario, what happens to the "C" value in ST_1 field? It seems like you are wanting to compress down the column for all rows where ID_2 = 1 (if I understand you correctly) but you haven't defined how values should be combined if there are more than one in the column. like, should it return "AC" or just "A" or just "C"?

 

[Sleidia]

Hello shadedecho !

You're totally right, there is an error in my scenario.
In fact, let's get rid of the 3rd row and let's retrieve only A from ST_1, B from ST_2 and 1 from ID_2. In a single row of course.

In that case, is it possible?

 

[shadedecho]

(yes, i think there is a way)... but first, question:

the "blank" values that appear in your table, are those "null" fields, or empty fields (like "&quot, or either?

 

[Sleidia]

Hello shadedecho.

For your question, these values can be anything as long as there is a solution to what I'm trying to achieve. Furthermore, the table doesn't exist yet. Everything will depend on what is possible and what isn't.

I'm curious to see what method you are thinking about, because in my opinion such a thing isn't possible

"See" you!

 

[shadedecho]

Ok, here's my solution, though because of a little lack of information on your particular situation, this may not be exactly what you are needing -- maybe this could give you and idea to run with, though.

The assumptions and caveats that make this work:

1. for each ID_2 (1,2,3,4) your table only has one row that has a value in the "ST_3" column, and one row (either the same row or a different one) that has a value in the "ST_4" column. This would rule out, as I said in a previous question, a situation where you had multiple values in the ST_3 column (A, C) for the same ID_2 (2).

2. Your result set would only return rows that had a value for both ST_1 and ST_2 that it could find. If either ST_1 or ST_2 were not being set for a particular ID_2 value, then that row would not be returned. This may or may not be the behavior you could or would want to use.

so, given the following table:

mytable:

+--------+--------+--------+--------+
|  ID_1  |  ID_2  |  ST_1  |  ST_2  |
+--------+--------+--------+--------+
|    1   |    1   |    A   |        |
|    2   |    1   |        |    B   |
|    3   |    2   |    C   |        |
|    4   |    3   |        |    D   |
|    5   |    3   |    E   |        |
|    6   |    2   |        |    F   |
|    7   |    4   |    H   |    I   |
|    8   |    5   |        |    J   |
+--------+--------+--------+--------+

SELECT m1.ID_1,
       m1.ID_2, 
      (CASE WHEN m1.ST_1 <> &quot;&quot; THEN m1.ST_1 END) AS 'ST_1', 
      (CASE WHEN m2.ST_2 <> &quot;&quot; THEN m2.ST_2 END) AS 'ST_2' 

FROM `mytable` m1 
     LEFT JOIN `mytable` m2 ON m1.B = m2.B 

HAVING (val_ST_1 <> &quot;&quot; AND val_ST_2 <> &quot;&quot;)

would produce:

+--------+--------+--------+--------+
|  ID_1  |  ID_2  |  ST_1  |  ST_2  |
+--------+--------+--------+--------+
|    1   |    1   |    A   |    B   |
|    3   |    2   |    C   |    F   |
|    5   |    3   |    E   |    D   |
|    7   |    4   |    H   |    I   |
+--------+--------+--------+--------+

Notice how the ID_2's didn't have to be any order, or grouped together, it found the C and the F values for ID_2 = 2 and put them together. Also, notice that on the ID_1 = 7 row, both H and I were present, so it just returned that row, and finally, notice that for ID_1 = 8, there was only found an ST_2 and not an ST_1, so that row didn't get returned.

(side note: there may be a way to change the HAVING clause, or combine it with an ORDER BY clause, to remove the assumption/caveat #2 above, though I'm not sure about that... I'll continue to play around with it and see if I can make something happen, and if so, I will post what I find. Likewise, as this problem has intrigued me quite a bit, I hope you'll post anything you new you find to the solution!)

Good luck, hope this helps get you on the right track!

 

[Sleidia]

Hello shadedecho!

Thank you from the heart for that very long post (and the work behind).

Although I've read the manual several times, surprinsingly, I've never noticed the CASE clause!
That was a big surprise today. And thanks to you!

Unfortunately, the lack of patience on my side, made me considere a different table structure and a more important PHPzation of the site

Anyway, I'll play around with that clause and use it for the next projects.

If there is anything I can do for you, let me know ( have a look to

http://www.humandesigner.com

to see what I could possibly do for you ).