Question 21032 in Mysql Question posted in
The official MySQL documentation can be found here.

Combine mysql result into 1 row

table img

SELECT namaline, shift1 FROM semualine WHERE cektelatshift1 <> 'Ontime' UNION
SELECT namaline, shift2 FROM semualine WHERE cektelatshift2 <> 'Ontime' UNION
SELECT namaline, shift3 FROM semualine WHERE cektelatshift3 <> 'Ontime';
namaline shift1
Line 1 Shift 1
Line 2 Shift 1
Line 3 Shift 1
Line 1 Shift 2
Line 2 Shift 2
Line 3 Shift 2
Line 1 Shift 3
Line 2 Shift 3

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
Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    Thank for all the answer. Sorry, I'm still new here should show my DB and used english. I figure out the close answer.

    SELECT shift1 AS shift, GROUP_CONCAT(`namaline` SEPARATOR ', ') AS 'line'  
FROM semualine 
WHERE cektelatshift1 <> 'Ontime' 
UNION
SELECT shift2 AS shift, GROUP_CONCAT(`namaline` SEPARATOR ', ') AS 'line'  
FROM semualine 
WHERE cektelatshift2 <> 'Ontime' 
UNION 
SELECT shift3 AS shift, GROUP_CONCAT(`namaline` SEPARATOR ', ') AS 'line'  
FROM semualine 
WHERE cektelatshift3 <> 'Ontime';
    shift line
    Shift 1 Line 1, Line 2, Line 3
    Shift 2 Line 1, Line 2, Line 3
    Shift 3 Line 1, Line 2

  2. 0

    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

    Login or Signup to reply.
  3. 0

    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');
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search