Solved

How to add specific columns of data in MySQL

Posted on 2011-02-25
6
309 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 142

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 142

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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

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 142

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-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
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…
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

863 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now