SQL list columns that contains '1' by given row

Question

My app reads txt files created by the user, and containing the query text, description, input and output, type of query etc. So I can't just make it easier getting data from the DB and elaborate them in java (which I'm more advanced in).

I have a spare_parts table which is an import from an excel file through CSV import in HEIDI SQL.

It's made this way:

+-------------+---------+---------+---------+---------+---------+
| PART NUMBER | MODEL X | MODEL Y | MODEL Z | MODEL 1 | MODEL 2 |
+-------------+---------+---------+---------+---------+---------+
| PART A      |       0 |       0 |       1 |       0 |       0 |
| PART B      |       1 |       0 |       0 |       1 |       0 |
| PART C      |       1 |       1 |       1 |       0 |       0 |
| PART D      |       0 |       0 |       0 |       1 |       1 |
+-------------+---------+---------+---------+---------+---------+

I need to list the models where a certain part is used, for instance: PART C is used to build model X, Y and Z.

I don't want to list all the columns manually, because they are a lot and because they change often.

What do you suggest?

Answer 1

Normalise your schema:

CREATE TABLE normalised_parts (
  PRIMARY KEY (model, part)
) SELECT 'X' AS model, `PART NUMBER` AS part FROM spare_parts WHERE `MODEL X`
UNION ALL
  SELECT 'Y', `PART NUMBER` FROM spare_parts WHERE `MODEL Y`
UNION ALL
  SELECT 'Z', `PART NUMBER` FROM spare_parts WHERE `MODEL Z`
UNION ALL
  SELECT '1', `PART NUMBER` FROM spare_parts WHERE `MODEL 1`
UNION ALL
  SELECT '2', `PART NUMBER` FROM spare_parts WHERE `MODEL 2`
-- etc.

Then your problem reduces to:

SELECT model FROM normalised_parts WHERE part = 'PART C'

If the model columns are unknown/numerous, you can create the above DDL from the information schema and then prepare and execute it:

SELECT CONCAT('
  CREATE TABLE normalised_parts (
    PRIMARY KEY (model, part)
  ) ', GROUP_CONCAT('
    SELECT ', QUOTE(COLUMN_NAME), ' AS model, `PART NUMBER` AS part',
    ' FROM spare_parts WHERE `', REPLACE(COLUMN_NAME, '`', '``'), '`'
    SEPARATOR '
  UNION ALL'))
INTO  @sql
FROM  INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME   = 'spare_parts'
  AND COLUMN_NAME LIKE 'MODEL %'  -- or whatever is appropriate

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Note that you may need to increase the value of group_concat_max_len, depending on the aggregate length of the column names.