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

Pivot a table using MySQL 5

Let’s say I have this MySQL table:

+--------------+----+-----------------+------------+
| val_critical | qs | code_critical   | code_line  |
+--------------+----+-----------------+------------+
| 1            | YS | 4.01 - Passers  | GX10-13686 |
| 1            | YS | 3.03 - Chains   | GX10-13686 |
+--------------+----+-----------------+------------+

I would like to get a table with the values rows of code_critical in the columns.

So far I was able to get an output like this:

+------------+----------------+---------------+
|  code_line | 4.01 - Passers | 3.03 - Chains |
+------------+----------------+---------------+
| GX10-13686 |       1        |       0       |
| GX10-13686 |       0        |       1       |
+------------+----------------+---------------+

Instead of

+------------+----------------+---------------+
|  code_line | 4.01 - Passers | 3.03 - Chains |
+------------+----------------+---------------+
| GX10-13686 |       1        |       1       |
+------------+----------------+---------------+

Using this query: SQL Fiddle

I personalized this good answer:
T-SQL Pivot? Possibility of creating table columns from row values

Tags:

2

Answers


  1. 0

    Now, your current output table like:

    Lets consider table name: Seat_Marker

    enter image description here

    Next update your query like:

    select code_line,
  CASE 
    WHEN(REGEXP_INSTR(GROUP_CONCAT(DISTINCT Chains), '1', 1) > 0) THEN 1 
    ELSE 0 
  END AS Chains,
  CASE 
    WHEN(REGEXP_INSTR(GROUP_CONCAT(DISTINCT Passers), '1', 1) > 0) THEN 1 
    ELSE 0 
  END AS Passers
  from Seat_Marker
group by code_line

    It will return data like:

    enter image description here

    Sample Code: db<>fiddle

    Login or Signup to reply.
  2. 0

    Your existing attempt was almost there. If you remove code_critical from the GROUP BY clause it will work.

    The val_critical and qs criteria you are including in your conditional aggregation are unnecessary, as they are already applied in the WHERE clause.

    Also, instead of the SUM(CASE WHEN `code_critical` = 'value' THEN 1 ELSE 0 END) AS `value` construct you can abbreviate it to SUM(`code_critical` = 'value') AS `value` .

    SELECT GROUP_CONCAT( DISTINCT CONCAT( 'SUM(`code_critical` = ''', `code_critical`, ''') AS `', `code_critical`, '`' ) ) INTO @SQL
FROM `doTable_critical`;
    
SET @SQL = CONCAT( 'SELECT _q.`code_line`, ', @SQL, '
                    FROM `doTable_report` _t    
                    JOIN `doTable_critical` _q ON _t.`code_line` = _q.`code_line` 
                    WHERE _q.`val_critical` = 1 AND _q.`qs` = ''YS''  
                    GROUP BY _q.`code_line`
                    ORDER BY _q.`code_line` ASC' );

PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

    Here’s a db<>fiddle.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search