'Stuff' And 'FOR XML PATH' alternative in Mysql for the following stored prodedure of Ms-sql

Ratna picture Ratna · Dec 23, 2013 · Viewed 18.6k times · Source

I have the following query in Ms-Sql

  INSERT INTO tbl_web_price_update 

    Select bd_book_code,

    Case 
        When pd.bpd_price is null then  
            cast((a.bd_price*c.ptm_price) as numeric(10))   
        else
            cast((pd.bpd_price*c.ptm_price) as numeric(10))             
        end
    As Price

    from tbl_books_details a 
        inner join tbl_price_type_master c on a.bd_price_type = c.ptm_price_type_id 
        left join tbl_books_price_details pd on pd.bpd_book_code = a.bd_book_code 
    Where c.ptm_price_type_id = @price_type     

    SELECT distinct r.price, STUFF((SELECT distinct ','+ Cast(a.bd_book_code as varchar) FROM tbl_web_price_update a WHERE r.price = a.price FOR XML PATH(''), TYPE).value('.','VARCHAR(max)'), 1, 1, '') FROM tbl_web_price_update r

I have migrated from ms-sql to my-sql and i am unable to write an alternative query in Mysql. Please Help.

Answer

peterm picture peterm · Dec 23, 2013

It looks like you're looking for something like this

SELECT price, GROUP_CONCAT(DISTINCT bd_book_code) bd_book_code
  FROM tbl_web_price_update
 GROUP BY price

Here is SQLFiddle demo