sqlmysqlcasestring-concatenationgroup-concat

Group_CONCAT CASE multiply outputs

I have an SQL query with this line:

GROUP_CONCAT(CASE WHEN t3.ship=1 AND t4.item=0 THEN t2.item_name END ORDER BY item_id SEPARATOR '<br>') `My Item List`

Output Now: Lamp

It works perfectly, however I want to list out the item's number which is stored in item_no column. Expected Output: 1. Lamp

I tried to add something like this, but didnt work:

GROUP_CONCAT(CASE WHEN t3.ship=1 AND t4.item=0 THEN t2.item_no, '.' ,t2.item_name END ORDER BY item_id SEPARATOR '<br>') `My Item List`

How can I achive this?

Solution

  • You must concatenate item_no, '.' and item_name with CONCAT():

    ... THEN CONCAT(t2.item_no, '.', t2.item_name) END ...