Is there a way to pivot rows to columns in MySQL without using CASE?

Question

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

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      key       value
bob            hair        brown
bob            eyes        blue
jake            hair        brown
jake            height        6'2"

Into this:

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

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.

A possible duplicate of http://stackoverflow.com/questions/649802/how-to-pivot-a-mysql-entity-attribute-value-schema — Kangkan, Nov 02 '10 at 15:16
Does this help? http://en.wikibooks.org/wiki/MySQL/Pivot_table — Kangkan, Nov 02 '10 at 15:18
These examples work well if you know the rows ahead of time that you want to turn into columns. However if you don't know all the distinct values, these don't work as well. I hadn't seen the first link though, so thanks for that. It is a function I had not explored yet. — Scott, Nov 03 '10 at 23:23
Not knowing the column names in advance makes this more difficult...you can resort to dynamic SQL to achieve the results you want (load the distinct column names into a var or temp table and use it to build your Dynamic SQL statement). Dynamic SQL introduces some of it's own issues (security), so be aware what these issues are before going down this route. I can give you some semi-psuedo code if you need assistance writing it — Twelfth, Nov 29 '10 at 16:58
@M.E. if you have semi-psuedo code for this, you can put in the answers so it could help others looking for a solution — Scott, Dec 17 '10 at 16:31
here is another example of the question, though I still haven't found an easy way to do this for unknown new columns http://stackoverflow.com/questions/8920626/is-it-possible-to-use-crosstab-pivot-query-in-mysql or http://www.artfulsoftware.com/infotree/qrytip.php?id=523 — Scott, Apr 08 '13 at 01:20

score 2 · Accepted Answer · edited Nov 11 '14 at 13:27

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

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'

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

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

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

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.

Is there a way to pivot rows to columns in MySQL without using CASE?

1 Answers1

Linked