How can I use column values as column names in MySQL

Question

I have a table that looks like this:

|date      |category_id|val
|2010-08-09|1          |2
|2010-08-09|2          |45
|2010-08-10|3          |1500
|2010-08-10|2          |4

I would like to select from this table, that each category id is a column so the end result looks like this:

|date      |1    |2   |3 
|2010-08-09|2    |45  |NULL
|2010-08-10|NULL |4   |1500

Is something like this possible with a single SELECT statement or stored procedure, without using an extra table and without knowing all category_id values beforehand?

possible duplicate of mysql query to dynamically convert row data to columns — Rowland Shaw
– Rowland Shaw, Commented Sep 16, 2010 at 12:46
YES, but it would require scripting to build out your SQL-Select statement, but don't have MySQL readily available to build such simulation of a Cross-Tab resultset — DRapp
– DRapp, Commented Sep 16, 2010 at 12:48
What you're describing is known as transposing, cross-tabulating, or pivoting. Not sure if you can do this with a single SELECT, at least not in MySQL without another language doing some work for you. Check out this article for some ideas: onlamp.com/pub/a/onlamp/2003/12/04/crosstabs.html — sasfrog
– sasfrog, Commented Sep 16, 2010 at 12:52

Abe Miessler · Accepted Answer · 2010-09-16 14:35:33Z

1

I think you want to use MYSql's sign to do this. Here is a good link that does something very similar to what you are tryign to do:

http://en.wikibooks.org/wiki/MySQL/Pivot_table

answered Sep 16, 2010 at 14:35

Abe Miessler

85.6k104 gold badges323 silver badges496 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

chiborg Over a year ago

While I still need to know what values I have in the category_id column, this solution is easy enough to generate the SQL SELECT with a small script.

A.F. · Accepted Answer · 2025-11-20 20:53:56Z

0

Here is a possible solution.

http://www.futhark.ch/mysql/106.html (on archive.org. Original does not exist anymore.)

edited yesterday

A.F.

438 bronze badges

answered Sep 16, 2010 at 13:54

codingguy3000

2,86515 gold badges49 silver badges76 bronze badges

Collectives™ on Stack Overflow

How can I use column values as column names in MySQL

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related