Solved

How to add specific columns of data in MySQL

Posted on 2011-02-25
6
312 Views
Last Modified: 2012-05-11
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
Comment
Question by:timbo007
  • 3
  • 3
6 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 34978440
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
 

Author Comment

by:timbo007
ID: 34978631
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34978676
>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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:timbo007
ID: 34978793
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34978846
indeed, you cannot do that in 1 single pass...

0
 

Author Closing Comment

by:timbo007
ID: 34978892
Great thanks heaps, I learned a lot!
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
remote mysql 8 35
TSQL Challenge... 7 43
Setting variables in a stored procedure 5 46
SQL Query Syntax Assistance 2 34
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

713 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question