在不使用UNIONS的情况下，使用MySQL将行转换为列? [英] Transpose a row into columns with MySQL without using UNIONS?

问题描述

我有一个与以下内容相似的表:

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'

在MySQL中这可能吗?我无法进入应用程序层并执行此操作，因为我将这些信息输入到将基于ID进行索引的搜索引擎中.其他各种DBMS都具有PIVOT和UNPIVOT之类的东西.对于任何我想念的东西的见解，我将不胜感激.

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.

谢谢！

推荐答案

我从本书

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

假设您的表名是foo.

首先，创建一个名为pivot的表:

First, create a table called pivot:

CREATE Table pivot (
  count int
);

向该表中插入与要在foo中进行透视的列一样多的行.由于您要在foo中有三列要进行透视，因此请在透视表中创建三行:

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);

现在在foo和pivot之间进行笛卡尔联接，使用CASE根据计数选择正确的列:

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

SELECT foo.id, Case pivot.count
  When 1 Then cat
  When 2 Then one_above
  When 3 Then top_level
End Case
FROM foo JOIN pivot;

这应该给您您想要的东西.

This should give you what you want.

这篇关于在不使用UNIONS的情况下，使用MySQL将行转换为列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！