sql - Transpose a row into columns with MySQL without using UNIONS? -

I have a table that is similar to the following:

  id | Cat | A_bove | Top_level | 0 'printer' hardware 'computer'  

I want to be able to write a query, without using unions , which will return a result set to me this table What does it mean to move columns into rows, that is, I should have the result:

  id | Cat | 0 'printer' 0 'hardware' 0 'computer'  

Is it possible in MySQL? I can not go to the application level and can not execute it because I am feeding them in a search engine which will be an ID-based index. Various other DBMSs have something like PIVOT and Unpip. I would appreciate any insights missing something.

Mahmud

PS

I am considering the re-normalization of the database as the last option because it will not be a trivial task.

Thank you!

I got out of this book, page 284-286:

Mann Take that your table name is foo .

First, pivot :

  create table smoke (count int);  

Insert that row as several rows because there are columns that you want to pivot in foo since you have foo There are three columns, which you want to pivot, create three rows in the pivot table:

  Insert the axis values ​​(1); Pivot values ​​(2); Pivot values ​​(3);  

Now to select the correct column, now use a codec foo and smoke by using CASE On the basis of the counting count:

  SELECT foo.id, case pivot.count 1 when cat then 2 then an_boss 3 then foo JOIN fumes from top_level end episode;  

What do you want this?