Select row by multiple columns with multiple rows in mysql

Question

I'm new to Mysql. In one of my mysql tables, I have a row that two columns involved and I need to list user as multiple rows, I'm wondering if it is possible.

For instance, in mysql table1, there is only one user1 as below;

 id      username       value1       value2
 ===========================================
 1       user1          abc           def

I want to group user1 by pass1 and pass2 with an additional row as below;

 id      username       allvalues     value
 =============================================
 1       user1          value1          abc
 2       user1          value2          def

id number is not important at that point as long as it is uniqe.

Thanks in advance.

Consider amending the design of table1 so that it looks more like table2 to begin with. Note that id='2' is rather meaningless in the present context. — Strawberry
– Strawberry, Commented Aug 27, 2018 at 9:40

ScaisEdge · Accepted Answer · 2018-08-27 09:27:21Z

2

you could use a UNION

select  username, 'value1' allvalues , value1 
from my_table  
union  
select  username, 'value2' , value2
from my_table

answered Aug 27, 2018 at 9:27

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

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

Comments

Fahmi · Accepted Answer · 2018-08-27 09:27:17Z

1

Try this with UNION

select username,'value1' as allvalues, value1 as value from tablename
union
select username,'value2' as allvalues, value2 as value from tablename

answered Aug 27, 2018 at 9:27

Fahmi

37.5k5 gold badges26 silver badges32 bronze badges

1 Comment

linuxman Over a year ago

thanks. I did not know union is for this purpose. However, id numbers end up with the same as '1' that i need to increase id number +1 for every row.

Collectives™ on Stack Overflow

Select row by multiple columns with multiple rows in mysql

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related