Get some rows as columns

Question

I have 2 tables:

table1:
         |id|create_date|title  |type  |
         ------------------------------- 
         | 1|2015-07-20 |bla-bla|TICKET|

table2_meta:
         |id|table1_id|meta_name|meta_value|
         ----------------------------------- 
         | 1| 1       | status  | new      |
         | 2| 1       | priority| low      |
         | 3| 1       | owner   | alex     |

Now, i wish to select it like this:

            |id|create_date|title  |status|priority|owner|
            |1 |2015-07-20 |bla-bla|new   |low     |alex |

My solution is:

SELECT 
      t1.id,
      t1.event_date, 
      case when m.meta_name="status" then m.meta_value end as status,
      case when m.meta_name="priority" then m.meta_value end as priority,
      case when m.meta_name="owner" then m.meta_value end as owner
FROM table1 as t1
LEFT JOIN table2_meta as m
      on m.table1_id=t1.id 
WHERE t1.type='TICKET' 
GROUP BY t1.id

So, it works. But seems a litle bit ugly.

My question is:
Are there any other solutions to this select and how to make it more productive?

The other way is to have 3 JOINs (one for each meta field), which IMO is about as ugly as your solution (which gets a little less ugly with `IF`' instead of `CASE`). The multiple joins do have the benefit of not messing up other aggregation functions you might have in the query (assuming 1 value per meta field). — Vatev, Jul 15 '15 at 08:53
are these the only types of `meta_name` s; could meta_name s expand or is it static ? — amdixon, Jul 15 '15 at 08:55

score 1 · Answer 1 · answered Jul 15 '15 at 09:21

Hey try this in your case for pivoting of table SQLFIDDLE

set @sql = null;
select
  group_concat(distinct
    concat(
      'max(case when meta_name = ''',
      meta_name,
      ''' then value end) AS ',
      concat(meta_name)
    )
  ) into @sql
FROM table1 as t1
LEFT JOIN table2 as m
      on m.table1_id=t1.id 
WHERE t1.type='TICKET';

set @sql = concat('select t1.id,', @sql, ' FROM table1 as t1
LEFT JOIN table2 as m
      on m.table1_id=t1.id 
WHERE t1.type=''TICKET'' 
GROUP BY t1.id 
');

prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;

score 0 · Answer 2 · answered Jul 15 '15 at 09:14

0

In oracle you could:

Instead of case when you could use pivot.

SELECT * FROM table2
PIVOT (
meta_value &nbsp;FOR (meta_name) IN ('Status' AS status, 'Priority' AS priority, 'owner' AS Ownser))

answered Jul 15 '15 at 09:14

invoketheshell

3,819
2
20
35

Get some rows as columns

2 Answers2