Combine mysql result into 1 row-CodePudding

I want to combine all this result into 1 cell

myexpectation is

1 cell [Line 1 Shift 1, Line 2 Shift 1, Line 3 Shift 1, Line 1 Shift 2, Line 1 Shift 1, Line 2 Shift 2, Line 1 Shift 3, Line 2 Shift 3]

CodePudding user response：

To achieve the output format you are looking for the following needs to be done

select GROUP_CONCAT(namaline, ' ', shift1) from semualine;

In GROUP_CONCAT space is needed between columns to have the space in the output as well

CodePudding user response：

The repeating pattern shift1, cektelatshift1, shift2, cektelatshift2, ... is a good indication of a table that would benefit from being normalized.

Both of these queries provide a result similar to your example (cannot be the same as your example is inconsistent). They differ in whether it is the row or column which is concatenated first.

SELECT
    GROUP_CONCAT(
        CONCAT_WS(', ',
            IF(cektelatshift1 <> 'Ontime', CONCAT_WS(' ', namaline, shift1), NULL),
            IF(cektelatshift2 <> 'Ontime', CONCAT_WS(' ', namaline, shift2), NULL),
            IF(cektelatshift3 <> 'Ontime', CONCAT_WS(' ', namaline, shift3), NULL)
        )
        SEPARATOR ', '
    )
FROM semualine
WHERE NOT (cektelatshift1 = 'Ontime' AND cektelatshift2 = 'Ontime' AND cektelatshift3 = 'Ontime');

SELECT
    CONCAT_WS(', ',
        GROUP_CONCAT(
            IF(cektelatshift1 <> 'Ontime', CONCAT_WS(' ', namaline, shift1), NULL)
            SEPARATOR ', '
        ),
        GROUP_CONCAT(
            IF(cektelatshift2 <> 'Ontime', CONCAT_WS(' ', namaline, shift2), NULL)
            SEPARATOR ', '
        ),
        GROUP_CONCAT(
            IF(cektelatshift3 <> 'Ontime', CONCAT_WS(' ', namaline, shift3), NULL)
            SEPARATOR ', '
        )
    )
FROM semualine
WHERE NOT (cektelatshift1 = 'Ontime' AND cektelatshift2 = 'Ontime' AND cektelatshift3 = 'Ontime');