CODEWITHSUNDEEP
mysqlsql
Nicolas
Nicolas

Reputation: 1848

mysql: merge two columns into two rows

I have the following statement

SELECT disease.id, disease.name, disease_synonym.name FROM disease JOIN disease_synonym where diseaseId=code

the result is a table with an id and two columns with the names. how can i transform this into 2 columns with only an id and the name? (of course, the id will now occur several times)

Bye,

Nico

Upvotes: 1

Views: 2557

Answers (2)

MatBailie
MatBailie

Reputation: 86716

Two ways come to mind...


Run the query twice (once for name, and once for synonym), then union the results together...

SELECT disease.id, disease.name FROM disease

UNION ALL

SELECT disease.id, disease_synonym.name FROM disease JOIN disease_synonym where diseaseId=code


Or join on a two row table, and use a CASE statement to do a pivot...

SELECT
  disease.id,
  CASE WHEN pivot.field = 'name' THEN disease.name
       WHEN pivot.field = 'syno' THEN disease_synonym.name
  END
FROM
  disease
INNER JOIN
  disease_synonym
    ON diseaseId=code
CROSS JOIN
  (SELECT 'name' AS field UNION ALL SELECT 'syno' AS field) AS Pivot

Upvotes: 4

sasfrog
sasfrog

Reputation: 2460

I think you want SELECT disease.id, disease_synonym.name FROM ... don't you? Just displaying the synonym names?

Upvotes: 0

Related Questions