MySQL concate 1 column from many records

Question

I have table , table_A:

Transnumber	lpost
A001	0
A002	1
A003	1
A004	1
A005	0
A006	1

I need to store transnumbers in one variable, with condition lpost=0 In MsSQl Server I can use :

SELECT @var1=@var1+','+rtrim(table_A.Transnumber) FROM table_A where lpost=0

In MySQL I try :

Set @var1='';
Select concat(@var1,'-',Transnumber) into @var1 FROM table_A where lpost=0 ;

It doesn't work

How the right syntax in MySQL? The result I expect : @var1 = A001,A005

Answer 1

You can use group_concat to help.

Consider:

set @var1 = (select group_concat(transnumber) from table_A where lpost = 0);

Then afterwards:

select @var1;

Gives:

Here's a dbfiddle with that example.

---

Edit:

As Akina pointed out, the SELECT ... INTO ... format can be used as well.

So for an example with this syntax:

SET @var1 = '';
select group_concat(transnumber) into @var1 from table_A where lpost = 0;

Will give the same result. See this updated fiddle with this 2nd example added.