Transpose a row into columns with MySQL without using UNIONS?

Question

I have a table that is similar to the following below:

       id |        cat |         one_above |        top_level | 
        0    'printers'          'hardware'        'computers'

I want to be able to write a query, without using unions, that will return me a result set that transposes this table's columns into rows. What this means, is that I want the result to be:

       id |          cat |
        0      'printers'
        0      'hardware'
        0     'computers'

Is this possible in MySQL? I can not drop down to the application layer and perform this because I'm feeding these into a search engine that will index based on the id. Various other DBMS have something like PIVOT and UNPIVOT. I would appreciate any insight to something that I'm missing.

Mahmoud

P.S.

I'm considering re-normalization of the database as a last option, since this won't be a trivial task.

Thanks!

Answer 1

After some fairly extensive digging I stumbled on this page which may or may not contain your answer. It's a difficult in MySQL but from a conceptual point of view I can construct a query that would transpose like this using describe (though it would probably perform horribly). So I'm sure that we can figure out a way to do it the right way.

Answer 2

I got this out of the book The Art of SQL, pages 284-286:

Let's say your table name is foo.

First, create a table called pivot:

create table pivot (
  count int
);

Insert into that tables as many rows as there are columns that you want to pivot in foo. Since you have three columns in foo that you want to pivot, create three rows in the pivot table:

insert into pivot values (1);
insert into pivot values (2);
insert into pivot values (3);

Now do a Cartesian join between foo and pivot, using a CASE to select the correct column based on the count:

select id, case count
  when 1 then cat
  when 2 then one_above
  when 3 then top_level
end case
from foo, pivot;

This should give you what you want.