BinaryWebPark

Use SELECT UNION and Merge Output Into Two Adjacent Columns of Label and Value

September 09, 2015

Combining SQL Query Data Into Two Columns

Recently, I had to structure a SQL query data on a particular subject into a two column format. I want to pull a the first name, last name, and email of a swimmer who placed first in a swim meet.

SQL Syntax

Below is the SQL query I used. I was looking for a fellow named Kip. This uses a hypothetical data set which you can generate for yourself at https://github.com/treble37/sql_modeler

select 'first_name' as chr, first_name as value from swimmers
  JOIN meet_relationships on swimmers.id = meet_relationships.swimmer_id
  JOIN swim_meets ON meet_relationships.swim_meet_id = swim_meets.id
  WHERE meet_relationships.swim_meet_rank = 1 AND swimmers.first_name = 'Kip'
  UNION select 'last_name' as chr, last_name as value from swimmers
  JOIN meet_relationships on swimmers.id = meet_relationships.swimmer_id
  JOIN swim_meets ON meet_relationships.swim_meet_id = swim_meets.id
  WHERE meet_relationships.swim_meet_rank = 1 AND swimmers.first_name = 'Kip'
  UNION select 'email' as chr, email as value from swimmers
  JOIN meet_relationships on swimmers.id = meet_relationships.swimmer_id
  JOIN swim_meets ON meet_relationships.swim_meet_id = swim_meets.id
  WHERE meet_relationships.swim_meet_rank = 1 AND swimmers.first_name = 'Kip'

If I run the above, then I end up with the following data set pulled:

<td>
  miles@carter.biz
</td>
<td>
  Kip
</td>
<td>
  Wintheiser
</td>
email
first_name
last_name

Try it for yourself

Use the sql_modeler rails application and use the rake db:seed command to generate your dataset. You may have to use a different name than ‘Kip’ since the names are randomly seeded.

Boot up the sqlite3 console

Type sqlite3 development.sqlite3 at the command prompt. Input the SQL statement above and check out the results.