GROUP_CONCAT using the CASE statement can produce multiple outputs.

Question

I have a SQL query that contains the following line of code:

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`

The current output is: Lamp. It's working fine, but I want the item number stored in the item_no column to be displayed in the list. The desired output is: 1. Lamp. I tried adding some code like this without success:

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 to achieve it?

Answer 1

You must use the CONCAT() function to connect item_no, '.' and item_name:

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