Concatenate SQL results into a single string with a counter

Question

Say I have a table as shown below

 Product_Id      Description
  BX01              Desc 1
  BxX1              Desc 2
  Dss3              Desc 3
  HHXY              Desc 4

I want the result exactly like: 1 - BX01, 2 - BxX1, 3 - Dss3, 4 - HHXY

I have this query:

DECLARE @ProID VARCHAR(8000)  
SELECT @ProID = COALESCE(@ProID + ' - ', '') + Product_Id FROM TABLE      
SELECT @ProID 

but the return values is only :

BX01,- BxX1,- Dss3,- HHXY.

The counting is lacking.

How to do that?

thanks

Answer 1

Try this instead:

SELECT GROUP_CONCAT( 
            CONCAT( @rownum := @rownum + 1, '-', Product_Id ) 
            SEPARATOR ',' 
       ) AS data
FROM table, ( select @rownum := 0 ) r

SQL Fiddle Demo

This will build the result you wanted by grouping all the column data and adding a counter before it, separating each data with a comma.

Note: You can remove , ( select @rownum := 0 ) r though. Depends on what you want.

Answer 2

In mysql it would be ,

SET @a:=0;

SELECT CONCAT( @a:=@a+1,'-',Product_Id) FROM TABLE ;