mysql查询将行数据动态转换为列 [英] mysql query to dynamically convert row data to columns

问题描述

我正在处理数据透视表查询. 架构如下

I am working on a pivot table query. The schema is as follows

Sno，名称，地区

同一个名称可能在许多地区出现，例如以样本数据为例

The same name may appear in many districts eg take the sample data for example

1 Mike CA
2 Mike CA
3 Proctor JB
4 Luke MN
5 Luke MN
6 Mike CA
7 Mike LP
8 Proctor MN
9 Proctor JB
10 Proctor MN
11 Luke MN

如您所见，我有一组4个不同的地区(CA，JB，MN，LP).现在，我想通过将名称映射到区域来获取为其生成的数据透视表

As you see i have a set of 4 distinct districts (CA, JB, MN, LP). Now i wanted to get the pivot table generated for it by mapping the name against districts

Name CA JB MN LP
Mike 3 0 0 1
Proctor 0 2 2 0
Luke 0 0 3 0

我为此写了以下查询

select name,sum(if(District="CA",1,0)) as "CA",sum(if(District="JB",1,0)) as "JB",sum(if(District="MN",1,0)) as "MN",sum(if(District="LP",1,0)) as "LP" from district_details group by name

但是，地区可能会增加，在这种情况下，我将不得不再次手动编辑查询并向其中添加新的地区.

However there is a possibility that the districts may increase, in that case i will have to manually edit the query again and add the new district to it.

我想知道是否存在一个查询，该查询可以动态获取不同地区的名称并运行上述查询.我知道我可以通过一个过程来完成并即时生成脚本，还有其他方法吗?

我之所以这样问，是因为查询"选择来自district_details 的区别(区)"的输出将使我在每一行上返回一个具有地区名称的列，我希望将其转置为列.

I ask so because the output of the query "select distinct(districts) from district_details" will return me a single column having district name on each row, which i will like to be transposed to the column.

推荐答案

您根本无法拥有返回可变列数的静态SQL语句.每次不同地区的数量发生变化时，您都需要构建这样的语句.为此，您首先执行

You simply cannot have a static SQL statement returning a variable number of columns. You need to build such statement each time the number of different districts changes. To do that, you execute first a

SELECT DISTINCT District FROM district_details;

这将为您提供包含详细信息的地区列表.然后，您构建一条遍历先前结果(伪代码)的SQL语句

This will give you the list of districts where there are details. You then build a SQL statement iterating over the previous result (pseudocode)

statement = "SELECT name "

For each row returned in d = SELECT DISTINCT District FROM district_details 
    statement = statement & ", SUM(IF(District=""" & d.District & """,1 ,0)) AS """ & d.District & """" 

statement = statement & " FROM district_details GROUP BY name;"

并执行该查询.然后，您需要在代码中处理可变数量的列

And execute that query. You'll then need have to handle in your code the processing of the variable number of columns

这篇关于mysql查询将行数据动态转换为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！