0

So I have this MySQL query which I'm trying to create in SQL Server. In MySQL I just use the function GROUP_CONCAT and in SQL Server I want to use STUFF.

MySQL

SELECT 
    GROUP_CONCAT(id
                    ORDER BY id DESC SEPARATOR '\', \'') AS id_list
FROM
    testjob
WHERE
    testjob.id IN (SELECT 
            MAX(testjob.id) AS id
        FROM
            testjob
                INNER JOIN
            target ON testjob.id = target.TestJobId
                LEFT JOIN
            dashboard_hidden ON testjob.TestSuiteCollectionId = dashboard_hidden.TestSuiteCollectionId
                INNER JOIN
            versions ON target.id = versions.TargetId
        WHERE
            target.Project = 'GiM1_0'
                AND testjob.Finished != 'PENDING'
                AND versions.Version IN ('V01.09.00.00110')
                AND IFNULL(dashboard_hidden.Hidden, 0) != 1
        GROUP BY testjob.id)
GROUP BY testjob.TestSuiteCollectionId DESC

I've tried to create the same in SQL Server using STUFF (I'm running MSSQL 2014, so cant use STRING_AGG)

SELECT
        id_list = 
        COALESCE(STUFF((
            SELECT '','' + CONVERT(VARCHAR(MAX), tj.id)
            FOR XML PATH('')
            ), 1, 1, ''), '')
FROM
    [SwMetrics].[testreportingdebug].testjob tj
WHERE
    tj.id IN (SELECT 
            MAX(tj.id) AS id
        FROM
            [SwMetrics].[testreportingdebug].testjob tj
                INNER JOIN
            [SwMetrics].[testreportingdebug].target tg ON tj.id = tg.TestJobId
                LEFT JOIN
            [SwMetrics].[testreportingdebug].dashboard_hidden dash ON tj.TestSuiteCollectionId = dash.TestSuiteCollectionId
                INNER JOIN 
            [SwMetrics].[testreportingdebug].versions v ON tg.id = v.TargetId
        WHERE
            tg.Project = 'GiM1_0'
                AND tj.Finished != 'PENDING'
                AND v.Version IN ('V01.09.00.00110')
                AND ISNULL(dash.Hidden, 0) != 1
        GROUP BY tj.id)     
GROUP BY tj.TestSuiteCollectionId
ORDER BY tj.TestSuiteCollectionId DESC

When I run this I get this error:

Msg 8120, Level 16, State 1, Line 4 Column 'SwMetrics.testreportingdebug.testjob.id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

The reason I GROUP BY testjob.TestSuiteCollectionId is because TestSuiteCollectionId have multiple IDs which is the same, but the id is unique and I want all those as a string. In this image you can see a MySQL with TestSuiteCollectionId and the grouped ids

wanted

Table without grouping:

#TestSuiteCollectionId, id
272, 37047
272, 36939
276, 37121
276, 36930
276, 37024
277, 37118
277, 37006
420, 37093
420, 36929
420, 37019
421, 37089
421, 36999
422, 37113
422, 37008
437, 37017
438, 37013
439, 37021
Dale K
  • 25,246
  • 15
  • 42
  • 71
Mads Sander Høgstrup
  • 668
  • 3
  • 18
  • @DaleK Okay thanks, thought I should tag all the code languages :) – Mads Sander Høgstrup Feb 11 '22 at 11:12
  • @DaleK Makes sense – Mads Sander Høgstrup Feb 11 '22 at 11:13
  • When string aggregating (in SQL Server), all `STUFF` does is remove the leading delimiter. It's `FOR XML PATH` that does the aggregation. – Thom A Feb 11 '22 at 11:15
  • Your subquery, with `FOR XML PATH` dosen't have a `FROM`, so it's referencing the tables in the outer `FROM`, hence the error. You need to use a correlated subquery. – Thom A Feb 11 '22 at 11:16
  • @Larnu as I wrote: I'm running `Microsoft SQL Server 2014`, so cant use `STRING_AGG` – Mads Sander Høgstrup Feb 11 '22 at 11:17
  • All SQL Server versions in mainstream support (ie SQL Server 2017 and later) use `STRING_AGG` for this. The `FOR XML` trick was used only in older versions – Panagiotis Kanavos Feb 11 '22 at 11:17
  • 1
    See [How to make a query with group_concat in sql server](https://stackoverflow.com/questions/17591490/how-to-make-a-query-with-group-concat-in-sql-server) and [String_agg for SQL Server before 2017](https://stackoverflow.com/questions/49361088/string-agg-for-sql-server-before-2017). Note the use of correlated subqueries. – Thom A Feb 11 '22 at 11:17
  • 1
    @MadsSanderHøgstrup SQL Server 2014 went out of support a long time ago. Why are you trying to migrate anything to it? This isn't a `yes but in an ideal world`. 8 years is a very long time. You can't install SQL Server 2014 legally, not even use a Dev version without paying for it. – Panagiotis Kanavos Feb 11 '22 at 11:18
  • @PanagiotisKanavos Well in the company we order a database, and I think they only support 2014, that's why we get that. – Mads Sander Høgstrup Feb 11 '22 at 11:23
  • @PanagiotisKanavos have written a mail to them if it is possible to get a 2017 database, just to check up, since its stupid to actually migrate to an outdated database. also its anoying not to be able to run newer functions lige `STRING_AGG`. So thanks for your comment :) – Mads Sander Høgstrup Feb 11 '22 at 11:27
  • @PanagiotisKanavos okay can get a 2019 version, thanks for making be ask them – Mads Sander Høgstrup Feb 11 '22 at 12:44

0 Answers0