CODEWITHSUNDEEP
mysqlsqlpivot
RedGiant
RedGiant

Reputation: 4748

Convert columns into rows with inner join in mysql

Please take a look at this fiddle.

I'm working on a search filter select box and I want to insert the field names of a table as rows.

Here's the table schemea:

CREATE TABLE general
    (`ID` int, `letter` varchar(21), `double-letters` varchar(21))
;

INSERT INTO general
    (`ID`,`letter`,`double-letters`)
VALUES
    (1, 'A','BB'),
    (2, 'A','CC'),
    (3, 'C','BB'),
    (4, 'D','DD'),
    (5, 'D','EE'),
    (6, 'F','TT'),
    (7, 'G','UU'),
    (8, 'G','ZZ'),
    (9, 'I','UU')
;


CREATE TABLE options
    (`ID` int, `options` varchar(15))
;

INSERT INTO options
    (`ID`,`options`)
VALUES
    (1, 'letter'), 
    (2, 'double-letters')
;

The ID field in options table acts as a foreign key, and I want to get an output like the following and insert into a new table:

 id   field   value
 1     1       A
 2     1       C
 3     1       D
 4     1       F
 5     1       G
 6     1       I
 7     2       BB
 8     2       CC
 9     2       DD
 10    2       EE
 11    2       TT
 12    2       UU
 13    2       ZZ

My failed attempt:

select DISTINCT(a.letter),'letter' AS field
from general a
INNER JOIN
options b ON b.options = field
union all
select DISTINCT(a.double-letters), 'double-letters' AS field
from general a
INNER JOIN
options b ON b.options = field

Upvotes: 0

Views: 1452

Answers (2)

Bertrand Ring
Bertrand Ring

Reputation: 39

I just made this query on Oracle. It works and produces the output you described : 

SELECT ID, CASE WHEN LENGTH(VALUE)=2THEN 2 ELSE 1 END AS FIELD, VALUE  
FROM (
SELECT rownum AS ID, letter AS VALUE FROM (SELECT DISTINCT letter FROM general ORDER BY letter)
UNION
SELECT (SELECT COUNT(DISTINCT LETTER) FROM general) +rownum AS ID, double_letters AS VALUE
 FROM (
    SELECT DISTINCT  double_letters FROM general ORDER BY double_letters)
)

It should also run on Mysql.

I did not used the options table. I do not understand his role. And for this example, and this type of output it seems unnecessary

Hope this could help you to.

Upvotes: 1

Brian DeMilia
Brian DeMilia

Reputation: 13238

Pretty sure you want this:

select distinct a.letter, 'letter' AS field
  from general a
 cross JOIN options b
 where b.options = 'letter'
union all
select distinct a.`double-letters`, 'double-letters' AS field
  from general a
 cross JOIN options b
 where b.options = 'double-letters'

Fiddle: http://sqlfiddle.com/#!2/bbf0b/18/0

A couple to things to point out, you can't join on a column alias. Because that column you're aliasing is a literal that you're selecting you can specify that literal as criteria in the WHERE clause.

You're not really joining on anything between GENERAL and OPTIONS, so what you really want is a CROSS JOIN; the criteria that you're putting into the ON clause actually belongs in the WHERE clause.

Upvotes: 1

Related Questions