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

How to return columns as "rows" in a table using MYSQL?

I have a Contact table that has columns: birthday, spouseBirthday, anniversary.  I am trying to do a query that will return all of these columns as rows and then allows me to filter by date.  For example, I may want to return a row for every date that is between "1/1/2010" and "1/1/2011".  The results may look like this:

ContactID  Date Type
1, 1/2/2010, Birthday
2, 1/4/2010, Anniversary
3, 3/2/2010, Birthday
3, 3/20/2010, SpouseBirthday
3, 6/15/2010, Anniversary

This is a very simple representation of the Contact table:

[Table - CONTACT]
ContactID
FirstName
LastName
SpouseFirstName
SpouseLastName
Birthday
SpouseBirthday
Anniversary

What is the most efficient way to do this?  Right now all I can think of is to use a bunch of UNIONS which sounds like a performance nightmare....
0
burtonrhodes
Asked:
burtonrhodes
  • 4
  • 3
  • 2
1 Solution
 
Walter RitzelSenior Software EngineerCommented:
Since you have a limited number of columns that's need to be transposed to rows, the most easy way would be:

select contactid, birthday as date, 'Birthday' as Type
from contact
union
select contactid, spousebirthday as date, 'SpouseBirthday' as Type
from contact
union
select contactid, anniversary as date, 'Anniversary' as Type
from contact
order by contactid
0
 
elimesikaCommented:
HI

I think the most effective way is to
1) Define a new table : EVENTS  with ContactID  Date Type columns
2) Use triggers on CONTACT to insert/update/delete relevant changes on EVENTS

Now you can easily select from the EVENTS table
If you generally ask for a date range , make an index on it for the EVENTS table
0
 
burtonrhodesAuthor Commented:
I will actually be using this across many columns as I have "Contact" and a "Transaction" table which has about 15 date columns to query.  Is using a UNION still the best way?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Walter RitzelSenior Software EngineerCommented:
I think it is the easiest way. i can point you to this link that may help you to find a programmatic way:
http://www.weberforums.com/topic14107.html
0
 
elimesikaCommented:
Another option is to use a Pivot table
see example on
http://en.wikibooks.org/wiki/MySQL/Pivot_table
0
 
burtonrhodesAuthor Commented:
elimsekia: I was looking at the link you sent and it appears I would like to do the exact opposite of what the tutorial is doing, but can't figure it out.  Can anyone offer some solid code that gets me in the right direction?  I appreacite the links to these FAQ/Tutorials, but I've been looking through these for 2 hours now and none of them have gotten me there.
0
 
Walter RitzelSenior Software EngineerCommented:
You have a solid code: the union for sure will work and it is basic ANSI SQL, so it is proved to work in any database.
0
 
burtonrhodesAuthor Commented:
Yes - but I am trying to avoid unions as stated in my original post.
0
 
burtonrhodesAuthor Commented:
Question not answered.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

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