CODEWITHSUNDEEP
mysqlsqlsql-server
Pavel Brun
Pavel Brun

Reputation: 71

Microsoft SQL Server equivalent of this MySQL query

So, I have the following MySQL stored procedure and I am tasked to create this same procedure in Microsoft SQL:

SET @sql = NULL;
SELECT  
  GROUP_CONCAT(DISTINCT
    CONCAT('MAX(IF(Record1 = ''',Record1,''', IsCompatible, NULL)) AS ''',
           CONCAT(pr.ProductName,' ', pr.ProductRelease), '''')
  ) INTO @sql
FROM
  ProductCompatibility pc
  LEFT JOIN Products as pr
       on pr.id = pc.Record1;

SET @sql = CONCAT(
              'SELECT pr.id, CONCAT(pr.ProductName,'' '', pr.ProductRelease) as Product, ', @sql, '
                 FROM ProductCompatibility pc 
                 LEFT JOIN Products as pr
                    on pr.id = pc.Record2 
                 GROUP BY pc.Record2');

PREPARE stmt FROM @sql;
EXECUTE stmt;

the following seems to parse correctly, but when it is executed, I am getting errors with the IF conditions. What would be the correct syntax for Microsoft SQL?

DECLARE @sql varchar(MAX), @finalsql varchar(MAX)

SET @sql = (SELECT  dbo.GROUP_CONCAT(DISTINCT
CONCAT('MAX(IF(pc.Record1 = ''',pc.Record1,''', pc.IsCompatible,
NULL)) AS ''',
           CONCAT(pr.ProductName,' ', pr.ProductRelease), '''')) FROM   [dbo].[ProductCompatibility] pc LEFT JOIN [dbo].[Products] as pr on
pr.id = pc.Record1);

SET @finalsql = CONCAT(
              'SELECT CONCAT(pr.ProductName,'' '', pr.ProductRelease) as Product, ', @sql, ' 
                    FROM compatibility_backup pc 
                 LEFT JOIN products_backup as pr
                    on pr.id = pc.Record2
                 GROUP BY pc.Record2');

EXECUTE(@finalsql);

Any pointers?

Upvotes: 1

Views: 60

Answers (2)

Pavel Brun
Pavel Brun

Reputation: 71

After a little more digging this is the correct format for the equivalent SQL Server function:

DECLARE @sql varchar(MAX), @finalsql varchar(MAX)

SET @sql = (SELECT
    dbo.GROUP_CONCAT(DISTINCT CONCAT('MAX(IIF(pc.Record1 = ''',pc.Record1,''', pc.IsCompatible, NULL)) AS ''',
           CONCAT(pr.ProductName,' ', pr.ProductRelease), ''''))
FROM 
    [dbo].[ProductCompatibility] pc LEFT JOIN [dbo].[Products] 
        as pr on pr.id = pc.Record1);

SET @finalsql = CONCAT(
              'SELECT CONCAT(pr.ProductName,'' '', pr.ProductRelease) as Product, ', @sql, ' 
                    FROM ProductCompatibility pc 
                 LEFT JOIN Products as pr
                    on pr.id = pc.Record2
                 GROUP BY pr.ProductName, pr.ProductRelease, pc.Record2');

EXECUTE(@finalsql);

Turns out that I have to add a couple other columns in the group by section display the SQL entries properly. And also use @Revenant solution in using IIF instead of IF.

Upvotes: 1

Revenant
Revenant

Reputation: 260

As for the IF errors, consider changing them to IIF if you're using SQL Server 2012 or later or CASE for lower versions.

Upvotes: 1

Related Questions