How to correctly translate MySQL query to MSSQL query using GROUP_CONCAT

Question

I'm trying to convert a MySQL DB to MSSQL DB but I'm experimenting some issues when translating the queries, in particular with the following:

MySQL

SELECT GROUP_CONCAT( parent.Name
ORDER BY parent.Lft
SEPARATOR  "«" ) AS Path, node.Description
FROM  `DB`.`Categories` AS node,  `DB`.`Categories` AS parent
WHERE node.Lft BETWEEN parent.Lft AND parent.Rgt AND node.ID = 2
GROUP BY node.Name, node.ID
ORDER BY node.Lft LIMIT 1;

And the result I got is this, which is the one I want to obtain:

+-------------------------------+
| Path                          |
+-------------------------------+
| Master Node « Vehicles « Cars |
+-------------------------------+

MS SQL

I tried to recreate the MySQL query using the suggestions from this post https://stackoverflow.com/a/5981860/2098159

SELECT STUFF((SELECT  ' « ' + [Name] FROM [DB].[dbo].[Categories] FOR XML PATH ('')), 1, 1, '') AS [Path] 
FROM [DB].[dbo].[Categories] AS node, [DB].[dbo].[Categories] AS parent 
WHERE node.[Lft] BETWEEN parent.[Lft] AND parent.[Rgt] AND node.[ID] = 7 
GROUP BY node.[Name], node.[ID], node.[Lft] ORDER BY node.[Lft];

And the result I got is this, no matter which ID I choose:

+---------------------------------------------+
| Path                                        |
+---------------------------------------------+
| « Master Node « Vehicles « Cars « Computers |
+---------------------------------------------+

At this point I only create a Table with 4 records: Master Node, Vehicles, Cars and Computers; just for testing.

I don't know what I'm missing. Does anyone has a suggestion?

Thanks in advance.

best way to get SQL answers is to create a SQLFiddle. – Mitch Wheat Jun 06 '15 at 03:03 — Mitch Wheat, Jun 06 '15 at 03:03
`LIMIT 1` is `TOP(1)` in SQL Server ;). – shA.t Jun 06 '15 at 03:24 — shA.t, Jun 06 '15 at 03:24

score 1 · Answer 1 · answered Jun 06 '15 at 03:29

The problem is that you're missing the criteria in the xml path part:

SELECT 
  STUFF((
    SELECT  ' « ' + [Name] 
    FROM [DB].[dbo].[Categories] C 
    WHERE C.ID = node.ID -- a criteria here is needed to get correct rows
  FOR XML PATH ('')), 1, 1, '') AS [Path] 
FROM 
  [DB].[dbo].[Categories] AS node, 
  [DB].[dbo].[Categories] AS parent 
WHERE 
  node.[Lft] BETWEEN parent.[Lft] AND parent.[Rgt] AND 
  node.[ID] = 7 
GROUP BY 
  node.[Name], 
  node.[ID], 
  node.[Lft] 
ORDER BY 
  node.[Lft];

My assumption was that ID is enough for identifying the correct rows.

Hello, thanks for your kind reply. You were right about the ID is enough. Nonetheless, I add the criteria you suggested but I only got one record. For instance, if i use the ID = 7, I only get `« Cars` , instead of `Master Node « Vehicles « Cars`. Thanks again. — Felipe Navarro, Jun 06 '15 at 15:02

score 0 · Answer 2 · answered Jun 09 '15 at 15:19

Here I place the correct query.

SELECT STUFF((SELECT CAST(' « ' AS varchar(MAX)) + parent.name
  FROM Categories AS node, Categories AS parent
 WHERE node.[Lft] BETWEEN parent.[Lft] AND parent.[Rgt]
   AND node.[ID] = 7
 ORDER BY node.lft
   FOR XML PATH('')), 1, 1, '') as Path

The only thing I can't be able to remove was the first '«'

+---------------------------------+
| Path                            |
+---------------------------------+
| « Master Node « Vehicles « Cars |
+---------------------------------+

How to correctly translate MySQL query to MSSQL query using GROUP_CONCAT

2 Answers2