Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    Regular Coder
    Join Date
    Mar 2005
    Location
    Brighton, UK
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Help with tricky crosstab query

    Hi, i need a hand with a cross tab query which i just cant work out, ive done crosstabs with numbers before, but i cant figure this out at all. I have this query:

    Code:
    SELECT variations.id, variations.price, options.variable_id, options.name
    FROM variations
    	LEFT JOIN variation_options ON variation_options.variation_id = variations.id
    	LEFT JOIN options ON options.id = variation_options.option_id
    WHERE variations.product_id = 2
    Which returns something like this:

    Code:
    variations.id	variations.price	options.variable_id	options.name
    -------------------------------------------------------------------------------------
    12		6546			1			Super Kingsize
    12		6546			2			Mattress Only
    12		6546			3			Latex
    12		6546			4			Soft
    13		321			1			Single
    13		321			2			Platform Top 2 Drawer
    13		321			3			Standard Sprung
    13		321			4			Soft
    What i want to go is group this result by the variations.id field so that there are two rows, and then turn the four different options.variable_id values into four columns with the options.name values as the values of those columns, so something like this:

    Code:
    variations.id	variations.price	variable_1		variable_2	variable_3	variable_4
    -------------------------------------------------------------------------------------
    12		6546			Super Kingsize		Mattress Only	Latex		Soft
    13		321			Single	Platform 	Top 2 Drawer	Standard Sprung	Soft
    Is this possible? Thanks in advance.
    Jack
    Last edited by Jak-S; 07-20-2006 at 12:12 PM.

  • #2
    Regular Coder
    Join Date
    Mar 2005
    Location
    Brighton, UK
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Ok, i worked out ONE way to do it, but im not convinced its the best way, this is it:

    Code:
    SELECT variations.id, variations.price,
    	GROUP_CONCAT(IF(options.variable_id = 1, options.name, NULL)) AS variable_1,
    	GROUP_CONCAT(IF(options.variable_id = 2, options.name, NULL)) AS variable_2,
    	GROUP_CONCAT(IF(options.variable_id = 3, options.name, NULL)) AS variable_3,
    	GROUP_CONCAT(IF(options.variable_id = 4, options.name, NULL)) AS variable_4
    FROM variations
    	LEFT JOIN variation_options ON variation_options.variation_id = variations.id
    	LEFT JOIN options ON options.id = variation_options.option_id
    WHERE variations.product_id = 2
    GROUP BY variations.id
    Im just not sure that GROUP_CONCAT is the right thing to use. Can it be done any better?

    EDIT: What about MAX, that works as well?

    Cheers,
    Jack
    Last edited by Jak-S; 07-20-2006 at 12:24 PM.

  • #3
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    Yes I think the GROUP_CONCAT will work for you.
    Last edited by guelphdad; 07-20-2006 at 04:01 PM.


  •  

    Posting Permissions

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