This project has moved. For the latest updates, please go here.

Problem with execution of the function GROUP_CONCAT

Jul 22, 2015 at 8:32 AM
Hello,

I have a sql query in my PHP code that works in mysql but it does not work in SQL server 2008
how to execute the function GROUP_CONCAT in my code PHP after the installation in SQL server 2008
AND there are other changes to make on sql server once the install finished ??!!!

Thank You
Coordinator
Jul 22, 2015 at 1:11 PM
Post your MySQL query and I'll help you port it to use this GROUP_CONCAT function in SQL Server.

Can you clarify what you meant by this?

AND there are other changes to make on sql server once the install finished ??!!!
Jul 22, 2015 at 1:30 PM
I installed the fonction but my script PHP does not still work

my Request :

SELECT dbo.GROUP_CONCAT("<span style=color:", IF([dbo].events_categories.color IS NOT NULL, [dbo].events_categories.color, "#000000"), ";>" + [dbo].events.name, "</span>") ORDER BY [dbo].calendar.event_time ASC SEPARATOR "$$" as cnt, [dbo].calendar.event_date, dbo.GROUP_CONCAT([dbo].events.id + "===" + [dbo].events.description) ORDER BY [dbo].calendar.event_time ASC SEPARATOR "$$") as id_description, SUBSTRING([dbo].calendar.event_date, 9, 2) as day FROM [dbo].calendar INNER JOIN [dbo].events ON [dbo].calendar.event_id = [dbo].events.id LEFT OUTER JOIN [dbo].events_categories ON [dbo].events.category_id = [dbo].events_categories.id WHERE SUBSTRING([dbo].calendar.event_date, 1, 4) = '2015' AND SUBSTRING([dbo].calendar.event_date, 6, 2) = '07' AND SUBSTRING([dbo].calendar.event_time, 4, 2) = '00' AND SUBSTRING([dbo].calendar.event_time, 1, 2) < '22' GROUP BY SUBSTRING([dbo].calendar.event_date, 9, 2)

[Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Incorrect syntax near the keyword 'IF'.
Coordinator
Jul 25, 2015 at 7:25 AM
Edited Jul 25, 2015 at 7:28 AM
There are multiple problems with your query before it will run on SQL Server, and even then you may not be able to get the same results.

First thing that jumps out is the use of IF which is not for use inline in SQL Server. Use a CASE expression in place of IF.

The second issue is your use of GROUP_CONCAT. While the intent of the SQLCLR aggregates I created is to give similar functionality on SQL Server it is not a drop-in solution that replaces all aspects of the MySQL version. For example SQL Server does not support declarative "ORDER BY" passed into a function call but I provide variants of GROUP_CONCAT where you can control the sort order of the group, e.g. GROUP_CONCAT_S.

Additionally my functions do not support a declarative "SEPARATOR" but again I supply a variant that allows you to control the delimiter, namely GROUP_CONCAT_D. I also provide a combination function that allows you to control both the sort order and delimiter, GROUP_CONCAT_DS.

Please read the information on the Documentation tab on this site and let me know if you have more questions.

Here is your query reformatted with the two calls to GROUP_CONCAT rewritten. There is a gap though in that my functions do not support sorting by a value other than the values being concatenated. For example this line in your query cannot be ported one-to-one to SQL Server using my functions.

dbo.GROUP_CONCAT([dbo].events.id + "===" + [dbo].events.description) ORDER BY [dbo].calendar.event_time ASC SEPARATOR "$$") as id_description

Since [dbo].calendar.event_time is not part of the group it cannot be used as the column to sort by. The below query might be the closest you can get. Let me know how it goes.
SELECT 
        dbo.GROUP_CONCAT_DS("<span style=color:" + CASE WHEN dbo.events_categories.color IS NOT NULL THEN [dbo].events_categories.color ELSE "#000000" END + ";>" + dbo.events.name + "</span>", N'$$', 1) as cnt, 
        dbo.calendar.event_date, 
        dbo.GROUP_CONCAT_DS(dbo.events.id + "===" + dbo.events.description, N'$$', 1) as id_description,
        SUBSTRING(dbo.calendar.event_date, 9, 2) AS day
FROM    dbo.calendar
        INNER JOIN dbo.events ON dbo.calendar.event_id = [dbo].events.id
        LEFT OUTER JOIN dbo.events_categories ON dbo.events.category_id = dbo.events_categories.id
WHERE   SUBSTRING(dbo.calendar.event_date, 1, 4) = '2015'
        AND SUBSTRING(dbo.calendar.event_date, 6, 2) = '07'
        AND SUBSTRING(dbo.calendar.event_time, 4, 2) = '00'
        AND SUBSTRING(dbo.calendar.event_time, 1, 2) < '22'
GROUP BY SUBSTRING(dbo.calendar.event_date, 9, 2);