CODEWITHSUNDEEP
mysqlsql
Acidgemcutter Agc
Acidgemcutter Agc

Reputation: 47

MySQL: Move data from multiple rows to one row based on column value

I have MySQL table in the following format. This is an output from a program that I run and I cannot change it.

+---+------------------------+
|   |  A    B   C   D    E   |
+---+------------------------+
|   | model amz wmt abt tgt  |
| 1 | c3000 100              |
| 2 | c3000     200          |
| 3 | c3000         150      |
| 4 | c3000              125 |
| 5 | A1234              135 |
| 6 | A1234     105          |
+---+------------------------+

I want to move all the rows into one single row based on the value in column 1 i.e model. The caveat is that the blank rows are not actually blank and contain a null character

DESIRED OUTPUT:

+---+-----------------------+
|   |  A    B   C   D    E  |
+---+-----------------------+
|   | model amz wmt abt tgt |
| 1 | c3000 100 200 150 125 |
| 2 | A1234 200 105     135 |
+---+-----------------------+

I tried using 

select model,group_concat(wmt),group_concat(amz)     
from table_name
group by model

And the output that I get is riddled with commas

+---+----------------------------------+
|   |  A                  B            |
+---+----------------------------------+
|   | model  amz          wmt          |
| 1 | c3000  ,,,,100,,,,   ,,,200,,,,  |
| 2 | A1234  ,,200,,,,,,  ,105,,,,,,   |
+---+----------------------------------+

Upvotes: 0

Views: 75

Answers (2)

Gurwinder Singh
Gurwinder Singh

Reputation: 39537

You can use TRIM and IF to convert blank values to null.

SELECT 
    model,
    GROUP_CONCAT(IF(TRIM(wmt) = '', NULL, wmt)),
    GROUP_CONCAT(IF(TRIM(amz) = '', NULL, amz))
FROM
    table_name
GROUP BY model

Upvotes: 1

Timothy Stepanski
Timothy Stepanski

Reputation: 1196

SELECT
   model,
   MIN(amz) AS amz,
   MIN(wmt) AS wmt,
   MIN(abt) AS abt,
   MIN(tgt) AS tgt
FROM
   table_name
GROUP BY
   model

Upvotes: 1

Related Questions