0

Please help me create a MYSQL query to use column data as column name in the output table:

Here is my table:

ID | Name | Stage
-------------------
1  | John | Stage 1
2  | Mark | Stage 3
3  | Bill | Stage 2
4  | Mary | Stage 2

I want to see this:

Stage 1 | Stage 2 | Stage 3
---------------------------
John    | Bill    | Mark
        | Mary    |
MM93
  • 13
  • 1
  • Is it MySql or ms-access? in both cases you will probably beter off using the built in pivot capabilities. read here for [MySql](http://stackoverflow.com/questions/7674786/mysql-pivot-table) or here for [ms access](http://stackoverflow.com/questions/16546305/pivoting-data-in-ms-access) – Zohar Peled Apr 28 '15 at 05:39
  • mysql has no built in pivot capabilities. you need to create them. further, all the mysql links i looked at in your link advocate exactly the same solution, they may just automate generating the query at the application level – pala_ Apr 28 '15 at 05:41

1 Answers1

2

This is your mysql query:

select
  case when stage = 'Stage 1' then name end 'Stage 1',
  case when stage = 'Stage 2' then name end 'Stage 2',
  case when stage = 'Stage 3' then name end 'Stage 3'
  from table1;

http://sqlfiddle.com/#!9/1ee5d/1

pala_
  • 8,901
  • 1
  • 15
  • 32
  • Thanks. That worked! Is there a way I can also accomplish this in MS Access? – MM93 Apr 28 '15 at 05:39
  • There was another answer here regarding that, but it looks like the author deleted it. here's another question about it: http://stackoverflow.com/questions/15137992/access-sql-with-pivot – pala_ Apr 28 '15 at 05:40
  • @ZoharPeled if you have 100 stages then you should probably be building the query in the application. like it or not, there is no magic 'pivot my data' functionality built into mysql. you can create a stored procedure to do it for you, if you like, but its not quite so trivial. – pala_ Apr 28 '15 at 05:44
  • oops, I'm actually an sql server user myself, just googled it and found the question, seen that the accepted answer got 72 votes so figured it's probably good. didn't bother to actually read it, a mistake I'll try not to repeat. – Zohar Peled Apr 28 '15 at 05:52
  • if it helps, i had to go and immediately research just in case i'd been 'doing it wrong' for years – pala_ Apr 28 '15 at 05:53