Translating MySQL statement to MS SQL (no GROUP_CONCAT)

Question

I'm trying to translate the query from my question in SQL multiple rows as columns (optimizing). It is in MySQL but I need it to also run on a MS SQL Server.

One problem is that there is no GROUP_CONCAT in MS SQL, but there seems to be ways to simulate this however (Simulating group_concat MySQL function in Microsoft SQL Server 2005?).

Also, I can't find a way to to store the first SELECT statement into the @sql variable the same way which troubles me as I don't know how to then reference colkey as I currently do.

The MySQL statement:

SET @sql = NULL;

SELECT
    GROUP_CONCAT(DISTINCT
        CONCAT('MAX(CASE
                WHEN ckm.colkey = ', colkey, ' THEN
                    (ccdr.value)
                END) AS ', CONCAT('`ExtraColumn_', colkey, '`'))
    ) INTO @sql
FROM test_customkeymapping;

SET @sql = CONCAT('SELECT c.Name, ', @sql, ' 
                   FROM customers c
                   LEFT JOIN customercustomdatarels ccdr
                     ON c.Id = ccdr.customer
                   LEFT JOIN customdatas cd
                     ON cd.Id = ccdr.customdata
                   LEFT JOIN test_customkeymapping ckm 
                     ON cd.key = ckm.customkey
                   GROUP BY c.Id');

PREPARE stmt FROM @sql;
EXECUTE stmt;

GarethD · Accepted Answer · 2014-07-03T11:45:29.457

In SQL Server you need to make the following changes

Explicitly declare your variable with a type
Use + to concatenate strings instead of CONCAT (Unless you are using SQL Server 2012 or later)
Use brackets ([]) for object names/aliases instead of backticks (``) - QUOTENAME will do this for you
Use XML extensions to concatenate rows
Include c.Name in the group by as it is contained in the select
Use SP_EXECUTESQL to actually execute your query

So your query becomes something like:

DECLARE @SQL NVARCHAR(MAX);

SET @SQL = 'SELECT c.Name' + (  SELECT  DISTINCT
                                        ', MAX(CASE WHEN ckm.colkey = ' 
                                        + QUOTENAME(colKey AS VARCHAR(10)) 
                                        + ' THEN (ccdr.value) END) AS ' 
                                        + QUOTENAME('ExtraColumn_' + CAST(colKey AS VARCHAR(10))
                                FROM    test_customkeymapping
                                FOR XML PATH(''), TYPE
                            ).value('.', 'NVARCHAR(MAX)') + 
            'FROM  customers c
                   LEFT JOIN customercustomdatarels ccdr
                     ON c.Id = ccdr.customer
                   LEFT JOIN customdatas cd
                     ON cd.Id = ccdr.customdata
                   LEFT JOIN test_customkeymapping ckm 
                     ON cd.[key] = ckm.customkey
                   GROUP BY c.ID, c.Name';

EXECUTE SP_EXECUTESQL @SQL;

Thank you! That worked perfectly with a few modifications. I had to remove QUOTENAME for colkey as it is an integer and add brackets to the 'key' column as it is a keyword. I have updated your answer. Again...thanks! — CleanUp, Jul 03 '14 at 11:40
No problem. I've updated the answer to add a cast from integer to varchar instead of removing quotename. — GarethD, Jul 03 '14 at 11:46

Translating MySQL statement to MS SQL (no GROUP_CONCAT)

1 Answers1