mysql: merge two columns into two rows

Question

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

SELECT disease.id, disease.name FROM ? Which name do you want disease.name or disease_synonym.name? — Nemoden
– Nemoden, Commented Jun 6, 2011 at 9:07
Both. Eventually i want something like this: Table(id, name): Values: (1 | disease.name) (1 | disease_synonym.name) ... sorry if this is not very readble, comments dont allow linebreaks — Nicolas
– Nicolas, Commented Jun 6, 2011 at 9:24
A union query may be: in the first query you select column 1 and 2; in the second query you select column 1 and 3, combine their results using UNION. — Salman Arshad
– Salman Arshad, Commented Jun 6, 2011 at 9:42

MatBailie · Accepted Answer · 2011-06-06 09:25:48Z

4

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

answered Jun 6, 2011 at 9:25

MatBailie

87.5k19 gold badges112 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nicolas Over a year ago

The first one works. already tried something in this direction but this ones better. thank you very much

sasfrog · Accepted Answer · 2011-06-06 09:20:11Z

0

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

answered Jun 6, 2011 at 9:20

sasfrog

2,4701 gold badge18 silver badges30 bronze badges

Collectives™ on Stack Overflow

mysql: merge two columns into two rows

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related