Mysql horizontal join, table values as columns

Question

I have 3 tables items, item_sizes, sizes with this fields:

items
id
name

item_sizes
item_id
size_id

sizes
id
size

The sizes have this values ex: S, M, XL, XXL etc I want to make a join or whatever to get this result:

item_id   |   name   |   S   |   M   |  XL  ...
--------------------------------------------
1             shirt      1       0      1
2             dress      0       1      1
3             jacket     1       1      1

And so on. The values 1 means that this item has this size. Is it possible to do this?

John Woo · Accepted Answer · 2013-02-01 10:01:27Z

1

SELECT  a.id,
        a.name,
        SUM(CASE WHEN c.size = 'S' THEN 1 ELSE 0 END) `S`,
        SUM(CASE WHEN c.size = 'M' THEN 1 ELSE 0 END) `M`,
        SUM(CASE WHEN c.size = 'L' THEN 1 ELSE 0 END) `L`,
        SUM(CASE WHEN c.size = 'XL' THEN 1 ELSE 0 END) `XL`,
        SUM(CASE WHEN c.size = 'XXL' THEN 1 ELSE 0 END) `XXL`
FROM    items a
        LEFT JOIN item_sizes b
            ON a.id = b.item_id
        LEFT JOIN sizes c
            ON b.size_ID = c.id
GROUP   BY a.id, a.name

use PreparedStatament

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(CASE WHEN c.size = ''',
      size,
      ''' then 1 ELSE 0 end) AS ',
      size
    )
  ) INTO @sql
FROM sizes;

SET @sql = CONCAT('SELECT  a.id,
                           a.name, ', @sql, ' 
                  FROM    items a
                          LEFT JOIN item_sizes b
                              ON a.id = b.item_id
                          LEFT JOIN sizes c
                              ON b.size_ID = c.id
                  GROUP   BY a.id, a.name');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

edited Feb 1, 2013 at 10:01

answered Feb 1, 2013 at 9:30

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Antonio Papa Over a year ago

No i want the sizes columns to be generated dynamically.Sizes can have 100 values i can't do that with case when.

Antonio Papa Over a year ago

I clicked the answer as useful but again it's not what i want.

Antonio Papa Over a year ago

or could be the answer hmmm by the way it's mysql :D

Collectives™ on Stack Overflow

Mysql horizontal join, table values as columns

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related