How to Split row into multiple columns in MySQL

Question

problem

Hi can somebody help me, i have some question on how to separate this row into columns with the same id number. I have tried some SQL function like GROUP_CONCAT() or SUBSTRING_INDEX() function but i dont know how it works

Output

I want the output to look something like this

please post your sample as formatted text inside your question (do not use images, pls) — etsa, Jun 06 '17 at 07:19
This question is different from the one linked to and claimed to 'already has an answer'. This deals with one initial table, while they other has several initial tables. That calls for different approaches when tackling them. — Gathide, Jun 02 '20 at 11:05

score 2 · Answer 1 · answered Jun 06 '17 at 07:30

2

It should resolve your problem:

INSERT INTO output SELECT
    input.ID,
    MIN(input.Unit_Cost),
    MAX(input.Unit_Cost)
FROM input
GROUP BY input.ID

answered Jun 06 '17 at 07:30

VirCom

3,414
1
10
10

Does this require creating the `field list` for `output` beforehand? – Gathide Jun 02 '20 at 11:08

score 0 · Answer 2 · answered Jun 06 '17 at 07:32

0

You might try a pivot table query:

SELECT 
    t1.`Unit_Cost` as `Unit_Cost1`,
    ifnull(t2.`Unit_Cost`,'') as `Unit_Cost2`
FROM table t1
LEFT JOIN table t2
ON t1.`ID` = t2.`ID`
GROUP BY `ID`
ORDER BY `ID`

answered Jun 06 '17 at 07:32

Sloan Thrasher

4,953
3
22
40

score 0 · Answer 3 · answered Jun 06 '17 at 07:36

I guess you can't separate it to columns, because then you could theoreticaly have infinite number of columns when the ID would be the same for infinite number of rows. But you can make GROUP_CONCACT() as you suggested and then separate the values in application with for example split() function.

SELECT ID, GROUP_CONCAT(Unit_Cost SEPARATOR ';') Unit_Cost
FROM CostTable
GROUP BY ID

Here are more examples with GROUP_CONCAT()

How to Split row into multiple columns in MySQL

3 Answers3