MySQL中的动态Crosstab查询

本文介绍了MySQL中的动态Crosstab查询的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

使用Crosstab Query从SQL Server切换到MySQL时出现问题。

I have a problem when switching from SQL Server to MySQL with Crosstab Query.

假设我有一个这样的表：

Suppose that I have a table like this:

| ID | BANKID | 1MONTH | 3MONTHS | 6MONTHS | 10MONTHS | 12MONTHS | 18MONTHS | 24MONTHS | 30MONTHS | 36MONTHS | |----|--------|--------|----------------|----------------|----------------|----------------|----------|----------------|----------------|----------------| | 1 | 1 | 3 | 2.900000095367 | 2.799999952316 | 2.700000047684 | 2.599999904633 | 2.5 | 2.400000095367 | 2.299999952316 | 2.200000047684 | | 2 | 2 | 5 | 4.900000095367 | 4.800000190735 | 4.699999809265 | 4.599999904633 | 4.5 | 4.400000095367 | 4.300000190735 | 4.199999809265 |

我想显示它：

BankID 1 2 1 Month 3 5 3 Months 2.9 4.9 6 Months 2.8 4.8 10 Months 2.7 4.7 12 Months 2.6 4.6 18 Months 2.5 4.5 24 Months 2.4 4.4 30 Months 2.3 4.3 36 Months 2.2 4.2

如何在MySQL中创建此类交叉表？

How can I create this kind of crosstab in MySQL?

您可以在此处测试数据： a href =sqlfiddle/#!9/9cf88/1 =nofollow> sqlfiddle/#!9/9cf88/1

You can test data at here : sqlfiddle/#!9/9cf88/1

谢谢！

推荐答案

使用以下查询

Set @Sq = NUll; Set @S =Null; SET @sql = NULL; SELECT GROUP_CONCAT(DISTINCT CONCAT( 'sum(CASE WHEN id = ', BankID,' THEN val end) AS "', BankID,'"') ) into @Sql FROM bankdeposit; SELECT GROUP_CONCAT(DISTINCT CONCAT( 'select id, ''', c.column_name, ''' as Bankid, ', c.column_name, ' as val from bankdeposit' ) SEPARATOR ' UNION ALL ' ) into @xSq FROM information_schema.columns c where c.table_name = 'bankdeposit' and c.column_name not in ('id','BankID', 'CreateDate', 'CreateBy', 'ModifyDate', 'ModifyBy', 'totalLoan','TotalDeposit','EstablishedYear','NumberOfStore') order by c.ordinal_position; SELECT GROUP_CONCAT(DISTINCT CONCAT( 'select id, ''', c.column_name, ''' as Bankid, ', c.column_name, ' as val from bankdeposit' ) SEPARATOR ' UNION ALL ' ) into @S FROM information_schema.columns c where c.table_name = 'bankdeposit' and c.column_name in ('totalLoan','TotalDeposit', 'EstablishedYear','NumberOfStore' ) order by c.ordinal_position; select CONCAT('select Bankid,',@sql,' from(select id, Bankid, val from (', @xSq,',',@S,') x order by id) xx group by Bankid order by length(BankID),BankID'); PREPARE stmt FROM @xSq; EXECUTE stmt; DEALLOCATE PREPARE stmt;

Sql Fiddle Here