How can I replace the MySQL code in MS-SQL

Question

I have written following code in Mysql. But now I want to run this same code in MS-SQL server. How do I do that?! I dont know some syntax in MS-SQL.

Code:

select 
    t2.gname 
from
(select 
    sum(t1.strength) as sst, 
    group_concat(t1.name order by t1.name) as gname 
    from 
    (select 
         a.name, 
         a.strength, 
         group_concat(d.zone order by d.zone) as zones 
         from Animals a 
         inner join Details d on a.id = d.id 
         group by a.id) t1 
    group by t1.zones) t2 
    order by t2.sst desc limit 1;

I got the correct output in MySQL. But how I can change it for MS-SQL?

Please help! Note question pertaining to this solution is : Problem statement

Hi Anjali, you're probably looking for [`STRING_AGG`](https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver15) as an equivalent in SQL Server to `group_concat` in MySQL. — TT., Sep 08 '21 at 05:53
Create a [fiddle](https://sqlfiddle.net) and play around till you get the result you want? — hd1, Sep 08 '21 at 05:53
@TT. Code is not working even after I replaced it with STRING_AGG! — Anjali, Sep 08 '21 at 05:56
Hi Anjali. I did not say that simply replacing the functions would work. Read the link for string_agg to get more details on how to use that function. — TT., Sep 08 '21 at 05:58
@TT. STRING_AGG ( expression, separator ) is the syntax. I understood that. But I am still getting error. — Anjali, Sep 08 '21 at 06:07
@Anjali - What error do you get? What have you tried yourself so far? `LIMIT` also doesn't exist in SQL Server. Instead, you can use a TOP 1. — SchmitzIT, Sep 08 '21 at 06:32
@SchmitzIT I have used top 1 instead of limit. But how do I replace group_concat? — Anjali, Sep 08 '21 at 06:44
@Anjali We wouldn't know, as you did not provide a sample dataset. — SchmitzIT, Sep 08 '21 at 06:46
@SchmitzIT I have given link to the problem statement in my question. there you can find sample dataset — Anjali, Sep 08 '21 at 07:03
`STRING_AGG` is available since SQL Server 2017 (14.x). You can check with the commande `select @@Version`. However you can use an hack based on [xml result](https://stackoverflow.com/a/13782812/11958063). As say by @SchmitzIT `LIMIT` doesn't exists use `TOP 1` or add row_number() column in subquery. — Tohm, Sep 08 '21 at 07:32
@Anjali this is a bad MySQL query to begin with and works only by chance - you can't have non-aggregate columns unless they appear in a `GROUP BY` clause. MySQL returns an *arbitrary* value in this case so only chance prevents `a.name` from having an arbitrary value. You can use `CROSS APPLY` instead of joining with `Details` so you don't have to use that expensive `GROUP BY a.Id`. — Panagiotis Kanavos, Sep 08 '21 at 07:58
`Select name,strength,(select group_concat(d.zone order by d.zone) from Details where Details.ID=Animals.ID) as Zones from Animals` This will return a single `Zones` value for each animal — Panagiotis Kanavos, Sep 08 '21 at 08:05
What I posted is valid in both databases, and eliminates the expensive `GROUP BY` in both — Panagiotis Kanavos, Sep 08 '21 at 08:10
As everyone already said, the equivalent is [STRING_AGG](https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver15) which was added in SQL Server 2017. The oldest SQL Server version still in mainstream support is SQL Server 2016 — Panagiotis Kanavos, Sep 08 '21 at 08:22
@Anjali Can you update the question with the version of SQL Server you are using? As per @Tohm, use `SELECT @@VERSION1 to find out the version you are running, and add it to the OP :) — SchmitzIT, Sep 08 '21 at 09:22

score 1 · Accepted Answer · answered Sep 08 '21 at 11:46

I would suggest:

select top (1) string_agg(a.name, ',') within group (order by a.name) as gname 
from (select a.name, a.strength, 
             string_agg(d.zone, ',') within group (order by d.zone) as zones 
      from Animals a join
           Details d
           on a.id = d.id 
      group by a.id, a.name, a.strength
     ) a
group by a.zones
order by sum(a.strength) desc;

In other words, the outer query is not needed -- either in MySQL or SQL Server.

score 0 · Answer 2 · answered Sep 08 '21 at 07:47

0

So pretty much limit > top 1 and group_concat > string_agg

I would've liked a way of testing this, but I think this is what you are after:

select top 1 t2.gname from
(select sum(t1.strength) as sst, 
string_agg(t1.name, '')
within group (order by t1.name) as gname 
from 
(select a.name, a.strength, 
string_agg(d.zone, ''
within group (order by d.zone) as zones 
from Animals a 
inner join Details d on a.id = d.id 
group by a.id) t1 group by t1.zones) t2 
order by t2.sst desc

answered Sep 08 '21 at 07:47

DaveWuzHere

213
2
7

'within group' doesn't exists in mssql!! I am getting error--- Msg 102, Level 15, State 1, Line 8 Incorrect syntax near 'within'. – Anjali Sep 08 '21 at 07:54
It's working for me in a database and table here. You are going to have to go for an xml stuff and do it the "old fashioned way": https://codemegeek.com/2018/11/17/use-stuff-and-for-xml-path-in-sql-server-to-concatenate-string/ – DaveWuzHere Sep 08 '21 at 08:03
@Anjali on the contrary, `WITHIN GROUP` does exist. The site you used targets an unsupported version of SQL Server. The earliest supported version of SQL Server is 2016 and `STRING_AGG` was added in 2017. We are in 2019 now – Panagiotis Kanavos Sep 08 '21 at 08:12

How can I replace the MySQL code in MS-SQL

2 Answers2