CODEWITHSUNDEEP
sqlmysqldatabasepivot
Calebe
Calebe

Reputation: 21

MySql with more then one Column

I have this table

------------------
1  | 20,00 | A  |
2  | 20,00 | A  |
3  | 20,00 | A  |
4  | 20,00 | A  |
1  | 50,00 | B  |
2  | 50,00 | B  |
3  | 50,00 | B  |
4  | 50,00 | B  |

I wold like to produce this one using group by. 

id   | A     | B     |
----------------------
1    | 20,00 | 50,00 |
2    | 20,00 | 50,00 |
3    | 20,00 | 50,00 |
4    | 20,00 | 50,00 |

Can you help me?

Upvotes: 2

Views: 89

Answers (3)

EboMike
EboMike

Reputation: 77752

Assuming that your table is "table_name", the first column is "id", the second column is "Value", the third column is "Type" and is an enum with 'A' or 'B':

SELECT
    a.ID,
    a.Value,
    b.Value
FROM table_name AS a,
    table_name AS b
WHERE a.ID=b.ID AND
    a.Type='A' AND
    b.Type='B'

Upvotes: 0

OMG Ponies
OMG Ponies

Reputation: 332661

It's a standard pivot query:

  SELECT t.id,
         MAX(CASE WHEN t.col = 'A' THEN t.value ELSE NULL END) AS A,
         MAX(CASE WHEN t.col = 'B' THEN t.value ELSE NULL END) AS B
    FROM TABLE t
GROUP BY t.id

MySQL doesn't support PIVOT/UNPIVOT syntax.

Upvotes: 5

Wolph
Wolph

Reputation: 80061

This doesn't look like a group by problem. But you can easily solve it with a join.

SELECT
  a.id,
  a.a,
  b.b
FROM table AS a
JOIN table AS b ON a.id = b.id
WHERE a.name = 'A' AND b.name = 'B'

Upvotes: 3

Related Questions