• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 322
  • Last Modified:

How to add specific columns of data in MySQL

say I have a table with 2 columns: `title` (VARCHAR) and `type` (VARCHAR)

In this case there is only 4 different `type`'s in the data set (1..4)

I want to perform a select query that breaks up `type` into individual columns, the output would be something like (in the code snippet):

I'm looking for 2 possible solutions, first is a simple SELECT query where I manually define the `type`s, and the second is a query where the `type`s are found and collumns automatically added... both would return the same results...

So for the first option, something to the likes of:

SELECT title, type_1=1 if `type` = 1,  type_2=1 if `type` = 2,  type_3=1 if `type` = 3,  type_4=1 if `type` = 4 FROM table_name

(in my example it doesn't need to return 0 I guess, I just put that there for readability)

The hard part I think is to make this happen without knowing how many types there are.. I have made the `types` collumn VARCHAR as the data could be anything, ie it doesn't have to be integers, but in this example it is..

The purpose of this is to go via an advanced data structure into the perl Module: HTML::Template which I can then use HTML_Templates IF functions like <TMPL_IF type_2><p>it's a drink!</p></TMPL_IF>
title       type_1  type_2  type_3  type_4
Hamburger   1       0       0       0
Coke        0       1       0       0
Fanta       0       1       0       0
Bread       0       0       0       1
Milk        0       0       0       1

Open in new window

0
timbo007
Asked:
timbo007
  • 3
  • 3
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this should do:
SELECT title
, max(case when type=1 then 1 else 0 end) type_1
, max(case when type=2 then 1 else 0 end) type_2
, max(case when type=3 then 1 else 0 end) type_3
, max(case when type=4 then 1 else 0 end) type_4
FROM table_name
group by title

Open in new window

0
 
timbo007Author Commented:
Hi yes, thanks this works as the first part of my question,

Regarding your answer though, can you please help me understand what or why it has to be done with the 'max' syntax?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Regarding your answer though, can you please help me understand what or why it has to be done with the 'max' syntax?
if you don't need to "group by", you can indeed remove the GROUP BY part and the MAX() part.

>the hard part I think is to make this happen without knowing how many types there are..
for that, you need to build up the SQL accordingly, checking all the existing values (possibly you have a lookup table for that?), and then only run the sql
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
timbo007Author Commented:
Ok, so are you saying I'm asking too much from MySQL to do that for me? Fair enough ;)

Using Perl I can automatically create the SQL based upon first scanning the table and building the case lines from that.. I was just hoping to do it all through SQL to make it more portable for the application I'm making and reduce queries... any thoughts, like would this really affect any performace things for many small tables being queried this way?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
indeed, you cannot do that in 1 single pass...

0
 
timbo007Author Commented:
Great thanks heaps, I learned a lot!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now