MySQL concate 1 column from many records

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

>Solution :

You can use group_concat to help.


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

Then afterwards:

select @var1;


enter image description here

Here’s a dbfiddle with that example.

Leave a Reply