SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Enthusiast
    Join Date
    Aug 2003
    Location
    Canada
    Posts
    93
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Select each subquery row as a column (or: column to row conversion)

    So, this one's got me stumped. Let's say I have these tables:

    Code:
    Table: Cars
    +-------+-------+
    | CarID | Make  |
    +-------+-------+
    | 1     | Ford  |
    | 2     | Chevy |
    | 3     | Dodge |
    +-------+-------+
    
    Table: People
    +-------+-------+---------+-------+
    | PerID | Name  | Phone   | Email |
    +-------+-------+---------+-------+
    | 1     | Alice | 1234567 | a@a.a |
    | 2     | Bob   | 8912345 | b@b.b |
    | 3     | Chuck | 5678912 | c@c.c |
    +-------+-------+---------+-------+
    
    Table: CarsOwnedByPeople
    +-------+-------+
    | PerID | CarID |
    +-------+-------+
    | 1     | 1     | 
    | 2     | 2     | 
    | 3     | 3     |
    | 3     | 2     |        
    +-------+-------+
    This isn't the actual data I'll be using (it's actually a educational group membership program), but I think this analogy is easier to explain.

    So, we can see that:

    Alice owns Ford, !Chevy, !Dodge
    Bob owns Chevy, !Ford, !Dodge
    Chuck owns Dodge, Chevy, !Ford

    Now, I need to get a list of ownership status of each car for each person. The catch is that I need to have them each in their own column (not CONCATed or GROUP_CONCATed into one column) and I'm trying to avoid doing a COUNT(*) on the Cars table.

    What I need to get as a result is:

    Code:
    +-------+---------+-------+------+-------+-------+
    | Name  | Phone   | Email | Ford | Chevy | Dodge |
    +-------+---------+-------+------+-------+-------+
    | Alice | 1234567 | a@a.a | 1    | 0     | 0     |
    | Bob   | 8912345 | b@b.b | 0    | 1     | 0     |
    | Chuck | 5678912 | c@c.c | 0    | 1     | 1     |
    +-------+---------+-------+------+-------+-------+
    So, basicaly, I need to convert the rows of the Cars table into columns in this results table.

    Is this even possible?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yeah, it's possible, but it's ugly and clumsy in sql

    please explain why it's so hard for you to do this in your application language (php or whatever), which is where cosmetic re-arranging of query results should be taking place...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Aug 2003
    Location
    Canada
    Posts
    93
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, yes, of course I can do this in PHP, but I figured it would be more eloquent and efficient with MySQL.

    Also, curiosity

  4. #4
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    it is the opposite, harder to do in sql, easier to do in the front end application. As r937 says, that's where cosmetic changes to the data belong.

  5. #5
    SitePoint Enthusiast
    Join Date
    Aug 2003
    Location
    Canada
    Posts
    93
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okeydokey.

  6. #6
    SitePoint Enthusiast
    Join Date
    Aug 2003
    Location
    Canada
    Posts
    93
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ...but just to the point of curiosity, how would one go about doing this within MySQL anyway?

  7. #7
    SitePoint Member
    Join Date
    Jan 2012
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It's called a PIVOT TABLE query. Google for that and you should be able to find both examples of such queries, and further evidence in support of why it's often wiser to handle this at the application level.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •