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.
We can use FOR XML PATH to prepare a comma-separated string from the existing data. Let's create an Authors table and insert a few records into it. In the data, we can see we have an ID column and the AuthorName column. If we just select the records, it gives the output in the following format.
The most common way to store XML in MySQL is to use the LOAD_FILE() function to open an entire XML document, store it in a variable, and insert the variable into a table column.
In a FOR XML clause, you specify one of these modes: RAW. AUTO. EXPLICIT.
MySQL :: MySQL 8.0 Reference Manual :: 13.2. 8 LOAD XML Statement.
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
GROUP_CONCAT()
function.If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With