将行透视成列 [英] Pivot rows into columns

问题描述

我有一个生成以下内容的SQL查询:

I have an SQL query that generates the following:

col1  | col2 | col3
=====================
item1 | key1 | value1
---------------------
item1 | key2 | value2

这是查询:

SELECT t1.col1, t2.col2, t2.col3
FROM table1 t1
JOIN table2 t2 ON t1.id = t1.table1_id

表1中的数据:

id | col1
=========
1  | item1
2  | item1

表2中的数据:

table1_id | col2 | col3
=====================
1         | key1 | item1
1         | key2 | item2
2         | key1 | item1
2         | key2 | item2
2         | key3 | item3

行数是动态的，因此在某些情况下我还可以得到以下信息:

The number of rows is dynamic, so I could also get the following in some cases:

col1  | col2 | col3
=====================
item2 | key1 | value1
---------------------
item2 | key2 | value2
---------------------
item2 | key3 | value3

我希望第一个查询结束的方式是

The way I want the first query to end up as is:

col1  | key1   | key2
=======================
item1 | value1 | value2

第二个查询将是:

col1  | key1   | key2   | key3
==============================
item2 | value1 | value2 | value3

是否有一种简单的方法来完成此任务而又不会过于复杂?我正在使用MariaDB.

Is there an easy way to do this without over-complexing stuff? I am using MariaDB.

推荐答案

我认为您需要此查询:

SET SESSION group_concat_max_len = 1000000;

SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(CASE WHEN t2.col2= ''',
      t2.col2,
      ''' THEN t2.col3 END) AS ',
      t2.col2
    )
   )INTO @sql
FROM  
     t1
JOIN  t2 ON t1.id = t2.table1_id
;


SET @sql=CONCAT('SELECT t1.col1, ',@sql,' FROM   
     t1
JOIN  t2 ON t1.id = t2.table1_id');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

FIDDLE

如果您有其他项目，则可能要按项目分组

If you have different items you probably want to group by item

FIDDLE

这篇关于将行透视成列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！