MySQL multiple values on own columns

Question

I have a table like so;

id1     id2
1       1
2       1
1       2
3       2
1       3
4       3
1       4
5       4

I'd like to select it in a way that I'd get rows GROUPed by id2, but still preserving both values of id1 for the corresponding rows in the table.

So I'd get a result set like so;

id1     id1     id2
1       2       1
1       3       2
1       4       3
1       5       4

I've never been even half good in advanced database queries -- how would I go about achieving this?

For every different value of id2, do only 2 values of id1 exist in the table? What if there are more? Or less (just one)? — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Jun 18, 2012 at 10:12
There are always exactly and precisely two values in id1 for every id2 :) — Emphram Stavanger
– Emphram Stavanger, Commented Jun 18, 2012 at 10:13

ypercubeᵀᴹ · Accepted Answer · 2012-06-18 10:14:00Z

2

If you have exactly 2 rows (with 2 values for id1) for every different value of id2, you can use this:

SELECT MIN(id1) AS id1_a
     , MAX(id1) AS id1_b
     , id2
FROM tableX
GROUP BY id2 ;

answered Jun 18, 2012 at 10:14

ypercubeᵀᴹ

116k19 gold badges181 silver badges247 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Marco · Accepted Answer · 2012-06-18 10:02:10Z

1

You could try using

SELECT id2, GROUP_CONCAT(id1) FROM your_table
GROUP BY id2

This way you have, for each id2 value, a column with all id1 values comma separated.
Take a look at GROUP_CONCAT syntax.

answered Jun 18, 2012 at 10:02

Marco

57.7k15 gold badges135 silver badges159 bronze badges

Comments

Ujjwal Prajapati · Accepted Answer · 2012-06-18 10:22:26Z

1

This might not be the perfect solution but in your case, it should work. This is a trick I used.

select id1, (sum(id1) - id1 ) as nID1, id2 from table_name group by id2

Hope it works. Ujjwal

answered Jun 18, 2012 at 10:22

Ujjwal Prajapati

2861 gold badge5 silver badges14 bronze badges

Collectives™ on Stack Overflow

MySQL multiple values on own columns

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related