24Carat
asked on
Run a simple DISTINCT and for each distinct result put values of 'different versions' in string.
Hi all you brilliant people!
i'm having this small problem here. i know the solution must be failry easy but my MySQL skills are quit basic.
So this is my problem.
I have a carpet productlist. Each carpet is available in certain sizes.
eg:
Artcode Sizes Description
AVG101 140x200 AvantGarde Cream
AVG101 170x240 AvantGarde Cream
AVG101 200x300 AvantGarde Cream
AVG101 300x400 AvantGarde Cream
AVG102 140x200 AvantGarde Red
AVG102 170x240 AvantGarde Red
AVG103 140x200 AvantGarde Blue
AVG103 170x240 AvantGarde Blue
AVG103 200x300 AvantGarde Blue
AVG104 140x200 AvantGarde Brown
AVG104 170x240 AvantGarde Brown
AVG104 200x300 AvantGarde Brown
AVG104 300x400 AvantGarde Brown
AVG104 400x500 AvantGarde Brown
Basicly i need a query that will give me a result with 3 columns such as this.
Artcode Description Available Sizes:
AVG101 AvantGarde Cream 140x200 ; 170x240 ; 200x300 ; 300x400
AVG102 AvantGarde Red 140x200 ; 170x240
AVG103 AvantGarde Blue 140x200 ; 170x240 ; 200x300
AVG104 AvantGarde Brown 140x200 ; 170x240 ; 200x300 ; 300x400 ; 400x500
I'm not sure if MySQL has a concat sum function.
Thanks
i'm having this small problem here. i know the solution must be failry easy but my MySQL skills are quit basic.
So this is my problem.
I have a carpet productlist. Each carpet is available in certain sizes.
eg:
Artcode Sizes Description
AVG101 140x200 AvantGarde Cream
AVG101 170x240 AvantGarde Cream
AVG101 200x300 AvantGarde Cream
AVG101 300x400 AvantGarde Cream
AVG102 140x200 AvantGarde Red
AVG102 170x240 AvantGarde Red
AVG103 140x200 AvantGarde Blue
AVG103 170x240 AvantGarde Blue
AVG103 200x300 AvantGarde Blue
AVG104 140x200 AvantGarde Brown
AVG104 170x240 AvantGarde Brown
AVG104 200x300 AvantGarde Brown
AVG104 300x400 AvantGarde Brown
AVG104 400x500 AvantGarde Brown
Basicly i need a query that will give me a result with 3 columns such as this.
Artcode Description Available Sizes:
AVG101 AvantGarde Cream 140x200 ; 170x240 ; 200x300 ; 300x400
AVG102 AvantGarde Red 140x200 ; 170x240
AVG103 AvantGarde Blue 140x200 ; 170x240 ; 200x300
AVG104 AvantGarde Brown 140x200 ; 170x240 ; 200x300 ; 300x400 ; 400x500
I'm not sure if MySQL has a concat sum function.
Thanks
ASKER
I've used this query:
SELECT
Productcode,
Description,
GROUP_CONCAT(Size SEPARATOR ', ')
FROM `productlijst`
GROUP BY
Productcode
Order By ProductCode ASC
The return that i get is :
Artcode Description Available Sizes:
AVG101 AvantGarde Cream (Memo)
AVG102 AvantGarde Red (Memo)
AVG103 AvantGarde Blue (Memo)
AVG104 AvantGarde Brown (Memo)
SELECT
Productcode,
Description,
GROUP_CONCAT(Size SEPARATOR ', ')
FROM `productlijst`
GROUP BY
Productcode
Order By ProductCode ASC
The return that i get is :
Artcode Description Available Sizes:
AVG101 AvantGarde Cream (Memo)
AVG102 AvantGarde Red (Memo)
AVG103 AvantGarde Blue (Memo)
AVG104 AvantGarde Brown (Memo)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi, i ve got it.
Appearantly it was some setting in my GUI.
I use Navicat MySQL. And somewhere in the settings is an option "show text BLOB fields in Data Grids".
I've enabled this and it works now.
Thanks a lot for your help.
Regards.
Appearantly it was some setting in my GUI.
I use Navicat MySQL. And somewhere in the settings is an option "show text BLOB fields in Data Grids".
I've enabled this and it works now.
Thanks a lot for your help.
Regards.
http://dev.mysql.com/doc/refman/5.1/en/group-by-functions.html#function_group-concat
It's very simple to implement. Try it out, and come back if you have any problems.