Converting SQL query to MySQL

Question

I have a SQL query where I am getting value of column in list form.

Select STUFF((
              Select Distinct ',' + QUOTENAME(visit_count) 
              From arms 
              Where armId= '@armIdValue'
              FOR XML PATH(''), TYPE
             ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

the value of visit_count column is 5, so I get result like this:

[1],[2],[3],[4],[5]

I tried to turn this query to run in MySQL but it's not accepting FOR XML.
Do any solutions ?

Fahmi · Accepted Answer · 2019-02-07 06:42:09Z

1

You need to use group_concat()

select group_concat(concat('[',visit_count,']')
from arms WHERE armId= 12 group by armid

edited Feb 7, 2019 at 6:42

answered Feb 7, 2019 at 4:43

Fahmi

37.5k5 gold badges26 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Mohammad Shahbaz Over a year ago

it doesn't give result in form of list, it simply returns 5

Fahmi Over a year ago

could you please add some sample data in your question @MohammadShahbaz

Mohammad Shahbaz Over a year ago

I have a table where 1 column is armId and other is visit_count, data is armId=12 and visit_count=5

Fahmi Over a year ago

@MohammadShahbaz, I've updated the answer - you can check but sample data is more appreciated

Mohammad Shahbaz Over a year ago

got 1,2,3,4,5 as result. only thing which i am not getting is the [ ]

|

Mano · Accepted Answer · 2019-02-07 09:27:34Z

1

You can try the Following Query.

MSSQL:

DECLARE @col nvarchar(MAX);
SELECT @col = COALESCE(@col,'') +'['+visit_count +']'+ ',' 
From arms Where armId= '@armIdValue'
SELECT @col

MYSQL :

SELECT armId,GROUP_CONCAT(visit_count)
 FROM arms Where armId= '@armIdValue'
 GROUP BY armId

edited Feb 7, 2019 at 9:27

answered Feb 7, 2019 at 5:26

Mano

7883 silver badges15 bronze badges

4 Comments

Mohammad Shahbaz Over a year ago

getting Error: DECLARE col nvarchar(MAX) Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE col nvarchar(MAX)' at line 1 0.000 sec

Mohammad Shahbaz Over a year ago

col is @col in the above statement

Mohammad Shahbaz Over a year ago

how can i run these threes statements together in mysql, it's giving me error

Mohammad Shahbaz Over a year ago

FUNCTION QUOTENAME does not exist 0.000 sec

Collectives™ on Stack Overflow

Converting SQL query to MySQL

2 Answers 2

6 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related