Solved

How to add specific columns of data in MySQL

Posted on 2011-02-25
6
308 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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

'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 …
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
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…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

757 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

18 Experts available now in Live!

Get 1:1 Help Now