有没有一种方法可以在不使用CASE的情况下将行旋转到列中? [英] Is there a way to pivot rows to columns in MySQL without using CASE?

问题描述

关于将各种数据库的行旋转为列的文章很多.他们似乎分为两个阵营，使用案例陈述或使用数据库供应商的内置功能.我使用的是 MySQL ，到目前为止，在任何内置函数上都找不到任何东西，该函数使我可以选择任意数量的行值，并希望将其转换为列.如果我不提前知道这些值，则无法构建经常出现在stackoverflow上的CASE查询.我想知道MySQL中是否有类似于其他数据库中称为交叉表或数据透视表的东西:

There are lots of posts out there on pivoting rows into columns for various databases. They seem to fall into two camps, using case statements or using a built in function of the database vendor. I am using MySQL and have not found anything so far on any built in function that will allow me to pivot on an arbitrary unknown number of row values that I want to pivot into columns. If I don't know the values ahead of time, I can't build the CASE queries that appear frequently on stackoverflow. I want to know if there is something in MySQL similar to these in other databases where it is called crosstab or pivot:

-Postgresql: http://www.postgresql.org/docs/current /static/tablefunc.html
-Oracle: http://www.oracle-base.com/articles/11g/PivotAndUnpivotOperators_11gR1.php
-SQL Server: http://msdn.microsoft.com/en-us/library /ms177410.aspx

-Postgresql: http://www.postgresql.org/docs/current/static/tablefunc.html
-Oracle: http://www.oracle-base.com/articles/11g/PivotAndUnpivotOperators_11gR1.php
-SQL Server: http://msdn.microsoft.com/en-us/library/ms177410.aspx

为了确保当我说将数据透视表转换为列时我想清楚我要的是什么，我想像这样变换一个表

Just to ensure that I'm clear what I'm asking for when I say pivot rows to columns, I want to transform a table like this

user_id    键    值
bob              bob                             眼睛       蓝色
杰克              杰克                                  高度         6'2"

user_id      key       value
bob            hair        brown
bob            eyes        blue
jake            hair        brown
jake            height        6'2"

对此:

user_id    头发眼睛高度
bob                                      蓝色       
杰克           棕色                    6'2"

user_id      hair       eyes       height
bob            brown      blue        
jake            brown                     6'2"

我正在寻找 MySQL 中的解决方案，因此，如果有任何特定的新数据库或新数据库可以解决此问题，将不胜感激.

I am looking specifically for a solution in MySQL, so if there is anything database specific that is new or coming out that you know about that can solve this it would be greatly appreciated.

推荐答案

如果您事先知道列名，Kangkan提供的链接将向您展示如何实现此目的.除了使用动态SQL生成语句外，我们将采用相同的逻辑.每个字段都需要包含两部分，select语句中的字段和获取值的适当联接...所以我们需要分两部分构建语句

The link that Kangkan provided will show you how to pull this off had you known the column names in advance. We're going for the same logic, except using dynamic SQL to build the statement. There is 2 parts to each field that you need to include, the field in the select statement and an appropriate join to get the value...so we'll need to build the statement in two parts

首先声明3个变量来构建它...在本示例中，我将使用@ select，@ join和@sql.给变量赋初始值

First declare 3 variables to build this...I'll go with @select, @join, and @sql for this example. Give the variables the initial values

 set @select = 'select user_id,'
 set @join = 'from table t'

现在在table.key字段中声明并加载具有不同值的游标.我将使用@field，因为变量使用独特的table.key字段填充.然后遍历它，构建两个变量:

now declare and load a cursor with the distinct values in the table.key field. I'm going to use @field as the variable gets populated with the distinct table.key field. Then loop through it building the two variables:

 set @select = @select + ', ' + @field + '.value as '+@field+'
 set @join = @join + ' left join table ' + @field + 'on '+@field+'.key = t.key and and '+@field+'.user_id = t.user_id

(联接被设计为使用@field中的值作为表的别名)

(the join is designed to use the value in @field as the alias of the table)

循环遍历光标，构建出@select和@join.循环结束时:

loop through your cursor building out @select and @join. At the end of the loop:

set @sql = @select + @join + 'where clause if you want'
exec @sql

以这种方式构建的动态SQL可能非常麻烦，难以解决(并解决问题)并提出安全问题……但这是我看到这一目标的唯一途径.注意变量的大小限制....如果那里有太多不同的键，则变量会变得太大.抱歉，我不能更准确地使用伪代码...您会发现在sql中构建动态sql十分艰辛.

Dynamic SQL built like this can be an absolute pain to trouble shoot (and get right) and open up security issues...but it's about the only way I can see this accomplished. Watch for size restrictions on your variables....if you have too many distinct Key's there, the variables grow too big. Sorry I can't be more exact with the pseudo on this...you'll find building dynamic sql in sql is painstaking.

这篇关于有没有一种方法可以在不使用CASE的情况下将行旋转到列中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！