0

I need help to convert below query to MS SQL from my sql

SELECT 
            md.ID AS `ID`,  
            t1.Name AS `Name`, 
            group_concat(IF(t2.Column3 = 'Value1',   t1.Value, NULL)) AS `Value1`,
            group_concat(IF(t2.Column3 = 'Value2',   t1.Value, NULL)) AS `Value2`,
            group_concat(IF(t2.Column3 = 'Value3',   t1.Value, NULL)) AS `Value3`
            FROM Table1 t1
            LEFT JOIN Table2 AS t2 ON t1.Column1 = t2.Column1
            GROUP BY t1.Id  ;
Maulik
  • 197
  • 1
  • 4

1 Answers1

0

You can use STRING_AGG(expression, ',') in place of GROUP_CONCAT(expression), as of SQL Server 2017. For older versions, there seem to be several ways to do it (including installing a GROUP_CONCAT function), none of which I know enough of SQL Server to understand; see Simulating group_concat MySQL function in Microsoft SQL Server 2005? and links in that question.

And you can use CASE WHEN ... THEN ... ELSE ... END (or also just CASE WHEN ... THEN ... END if the else result is NULL) in place of IF(..., ..., ...). CASE is also valid in mysql.

But you will need to group by t1.Name or select something like MAX(t1.Name) instead, since you can't use non-aggregate columns that aren't grouped by (just as you can't in modern versions of mysql using modern default settings).

fiddle

ysth
  • 96,171
  • 6
  • 121
  • 214
  • Thanks for the Answer but I have Sql server 2014 So I will not be able to use String_AGG function. – Maulik Jan 05 '21 at 12:56