Clash Royale CLAN TAG#URR8PPP

SQL: Mege two rows in one

On MySQL I'd like to merge 2 rows in 1, for example:

| Name | Value | Res |
| foo | Type | 0.2 |
| foo | Group | aaa |
| bar | Type | 0.3 |
| bar | Group | bbb |


my expected result would be:

| Name | Type | Group |
| foo | 0.2 | aaa |
| bar | 0.3 | bbb |


I'd like to merge two entries with some common values in one that has two column that distinct a value in a Res column on the value of a Type column. How can I do this?
Thanks

Res

Type

INNER JOIN

2 Answers
2

Your expect result need to do pivot try to use condition aggregate function to make it.

CREATE TABLE T(
Name varchar(50),
Value varchar(50),
Res varchar(50)
);



insert into t values ( 'foo' ,'Type' , '0.2');
insert into t values ( 'foo' ,'Group' , 'aaa');
insert into t values ( 'bar' ,'Type' , '0.3');
insert into t values ( 'bar' ,'Group' , 'bbb');


Query 1:

SELECT Name,
MAX(CASE WHEN Value= 'Type' THEN Res END) Type,
MAX(CASE WHEN Value= 'Group 'THEN Res END) `Group`
FROM T
GROUP BY Name


Results:

| Name | Type | Group |
|------|------|-------|
| bar | 0.3 | bbb |
| foo | 0.2 | aaa |


This answer from Stack Exchange - should be very useful.

To do this you will need to run two sub-queries, and then combine the results with a JOIN command.

Assuming your table is called T:

SELECT s.`Name`, s.`type`, p.`group`
FROM
(
SELECT `Name`, `Res` as `type`
FROM T WHERE `Value`="type1"
) s
LEFT JOIN
(
SELECT `Name`, `Res` as `group`
FROM T
WHERE `Value`="group1"
) p
ON s.`Name` = p.`Name`


I know that your example had the variable names changed for ease of reading - but be careful with your choices as they may be reserved words in MySQL.

This can be seen in this fiddle

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.