带动态列的单表mysql pivot

vojdkbi0 于 2021-06-24 发布在 Mysql

关注(0)|答案(2)|浏览(404)

我想把mysql表的行转换成列，通过mysql透视表我的输入表。我的输入表有以下格式的数据。

Area    Status   User
-----------------------
1       Active   user1
1       Failed   user2
1       Success  user4
2       Active   user2
2       Failed   user3
2       Success  user4

我想要的输出格式如下

Status   user1     user2   user3    user4
-----------------------------------------
Active   1         1       0         0
Failed   0         1       1         0
Success  0         0       0         2

因为我不知道用户的确切数量，所以我只想通过动态列来透视它。

mysql pivot pivot-table

来源：https://stackoverflow.com/questions/51220355/single-table-mysql-pivot-with-dynamic-column

2条答案

按热度按时间

oewdyzsn1#

我有另一个可运行的例子给你，从

product_id  supplier_id number      price
p1          s1          2           2.12
p1          s2          3           3.12
p2          s1          4           4.12

到

product_id  supplier_id1        supplier_id2        number1         number2             price1              price2
p1              s1              s2                  2               3                   2.12                3.12
p2              s1              NULL                4               NULL                4.12                NULL

这里的“supplier\u id”是动态的，它可能是100万个大数据集中的一个小数据集。因此可能有supplier1、supplier99或supplier999，这取决于源表中的内容。首先，让我们创建源表：

CREATE TABLE test
(
    product_id varchar(10),
    supplier_id varchar(10),
    number int(11),
    price decimal(10,2)
);
INSERT INTO test (product_id, supplier_id, number, price) VALUES ('p1', 's1', 2, 2.12);
INSERT INTO test (product_id, supplier_id, number, price) VALUES ('p1', 's2', 3, 3.12);
INSERT INTO test (product_id, supplier_id, number, price) VALUES ('p2', 's1', 4, 4.12);

我不认为一个select就可以做到这一点，所以需要temp表和列，下面的代码就是您需要的：

DROP TABLE IF EXISTS  final_data;
DROP TABLE IF EXISTS  temp_data;
CREATE  TEMPORARY TABLE temp_data
    SELECT
           product_id,
           IF(@productid = product_id, @rownum := @rownum + 1, @rownum := 1) seller_number,
           @productid := product_id,
           supplier_id,
           number,
           price
    FROM
         test
           CROSS JOIN (SELECT @rownum := 0) r
           CROSS JOIN (SELECT @productid:="") n
    ORDER BY
             product_id ASC;

ALTER TABLE temp_data ADD PRIMARY KEY(product_id, seller_number);
ALTER TABLE temp_data ADD INDEX (seller_number);

# Dynamic Pivot starts via prepared statements

# Step 1: Dynamily create colum names for sql

# Field supplier_id

SET @sql = NULL;
SELECT
       GROUP_CONCAT(DISTINCT
                    CONCAT(
                      ' MAX(IF( seller_number= ''',
                      seller_number,
                      ''', supplier_id, NULL)) AS ',
                      CONCAT("supplier_id", seller_number)
                        )
           ) INTO @sql
FROM temp_data;

# Field number

SELECT
       CONCAT(@sql, ', ',
              GROUP_CONCAT(DISTINCT
                           CONCAT(
                             ' MAX(IF( seller_number= ''',
                             seller_number,
                             ''', number, NULL)) AS ',
                             CONCAT("number", seller_number)
                               )
                  ) )INTO @sql
FROM temp_data;

# Field price

SELECT
       CONCAT(@sql, ', ',
              GROUP_CONCAT(DISTINCT
                           CONCAT(
                             ' MAX(IF( seller_number= ''',
                             seller_number,
                             ''', price, NULL)) AS ',
                             CONCAT("price", seller_number)
                               )
                  ) )INTO @sql
FROM temp_data;

# Step 2: Add fields to group by query

SET @sql = CONCAT(' create table final_data as (SELECT
                        product_id,
                        ', @sql, '
                    FROM
                        temp_data
                    GROUP BY
                        product_id)  ');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DROP TABLE IF EXISTS  temp_data;

赞(0）回复(0）举报 2021-06-24

7kjnsjlb2#

在您的例子中，如果您有一个单独的用户表，您可以轻松地在用户表和状态表之间生成笛卡尔积，并进行数据透视。。如果您需要更多帮助，请告诉我。。
在我下面的一篇关于销售报表透视schenerio的博文中，我正在使用动态日历表生成带有订单类别表的笛卡尔产品。。旋转和笛卡尔积示例

赞(0）回复(0）举报 2021-06-24