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
Now, your current output table like:
Lets consider table name: Seat_Marker
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:
Sample Code: db<>fiddle