Same ID Different Value become one row

Question

i need query for some value with same ID in many rows to become one row

this is myssql

select id,
case when a.id = b.id then b.name  else null end as ModelName
from Unit a
inner join Product b
on a.id = b.id 
where a.id = 36239

36239    PAN FEEDER 1000MM X 1500MM 
36239   NS 400 GYRATORY CONE CRUSHER    
36239   NH 400 TERTIARY CONE CRUSHER    
36239   SCALPING SCREEN 6'X12'X2 DECK   
36239    PRODUCT VIBRATING SCREEN 8'X20'X3 DECK 
36239   ADPLUS PAN FEEDER 900 X 1500    
36239   GRIZZLY FEEDER DHS 6

my expected result is

36239 PAN FEEDER 1000MM X 1500MM NS 400 GYRATORY CONE CRUSHER NH 400 TERTIARY CONE CRUSHER

That case expression makes no sense at all. The first condition will always be true. — jarlh, Sep 18 '19 at 08:24
Poor sample data. You should have a few different id values to make the problem clearer. — jarlh, Sep 18 '19 at 08:25

Zaynul Abadin Tuhin · Answer 1 · 2019-09-18T08:27:29.303

0

use group_concat()

select a.id ,group_concat(b.name SEPARATOR ' ')
from Unit a
inner join Product b
on a.id = b.id  
where a.id = 36239
group by a.id

edited Sep 18 '19 at 08:27

answered Sep 18 '19 at 08:24

Zaynul Abadin Tuhin

31,407
5
33
63

1

Put GROUP BY after WHERE. – jarlh Sep 18 '19 at 08:26
And qualify the select list's id column. – jarlh Sep 18 '19 at 08:27
Specify the separator for `group_concat()`: default is the comma, OP seems to want a space – GMB Sep 18 '19 at 08:27

Same ID Different Value become one row

1 Answers1