group concat different rows

Question

name   type   shape
---------------------
name1  type1  shape1
name1  type2  shape1
name1  type3  shape2

after query i need that result:

name    shape1Types     shape2Types
-----------------------------------
name1   type1, type2    type3

i can only think as much:

select name, group_concat(type) as shape1Types, group_concat(type) as shape2Types 
from table 
where name = 'name1' 
  and shape1Types in (select type from table2 where shape = 'shape1') 
  and shape2Times in (select type from table3 where shape = 'shape2') 
group by name

but here it says shape1Types is unknown column

Zaynul Abadin Tuhin · Accepted Answer · 2018-12-10 09:54:38Z

1

try like below by using case when

   select name, group_concat(case when shape='shape1' then type end) as shape1Types, group_concat(case when shape='shape2' then type end) as shape2Types 
    from table 
    group by name

answered Dec 10, 2018 at 9:54

Zaynul Abadin Tuhin

32.1k6 gold badges37 silver badges66 bronze badges

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

Comments

LukStorms · Accepted Answer · 2018-12-10 10:37:44Z

0

The alias names shape1Types & shape2Types are unknown in that WHERE clause.
Hence the error.

Instead of using IN you could JOIN to the unique types in those other 2 tables.

select 
 t.name, 
 group_concat(shape1.type) as shape1Types, 
 group_concat(shape2.type) as shape2Types 
from table1 t
left join (select type from table2 where shape = 'shape1' group by type) shape1 
  on shape1.type = t.type
left join (select type from table3 where shape = 'shape2' group by type) shape2 
  on shape2.type = t.type
where t.name = 'name1'
-- and (shape1.type is not null or shape2.type is not null)
group by t.name

edited Dec 10, 2018 at 10:37

answered Dec 10, 2018 at 10:17

LukStorms

29.8k5 gold badges36 silver badges49 bronze badges

Collectives™ on Stack Overflow

group concat different rows

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related