Problem with GROUP_CONCAT()

[sydu]

Hello all,

I have a problem with the new version of PATSTAT (5.05), before I worked with the 5.01.01 version.
With this previous version, I could use the GROUP_CONCAT() instruction of MySQL but with this new version, I could not.

To give an example, I used this instruction like this:

SELECT DISTINCT t201.appln_nr, t201.earliest_filing_year, t201.appln_auth, t206.person_ctry_code, t206.han_harmonized, t206.hrm_L2,
GROUP_CONCAT(DISTINCT t209.ipc_class_symbol) AS list_ipc_class_symbol
FROM tls201_appln t201
LEFT OUTER JOIN tls209_appln_ipc t209 ON t201.appln_id = t209.appln_id
LEFT OUTER JOIN tls207_pers_appln t207 ON t201.appln_id = t207.appln_id
LEFT OUTER JOIN tls206_person t206 ON t207.person_id = t206.person_id
WHERE t209.ipc_class_symbol LIKE 'XXXX'
AND t201.earliest_filing_year >= XXXX

The goal of this instruction is to have all IPC on the same row in the result table (A44C 15/00,A45C 11/18,A45F 5/00,…)
But with the 5.05 version, I have an error message: 'GROUP_CONCAT' is not a recognized aggregate function.

So, I would know if there is a mistake in my code or if there is a mean to have the same result with the new version of PATSTAT?

Thank you in advance.
Kind regards.

Sydu

[Geert Boedt]

Hello Sydu,
the group_concat instruction is proprietary to MySQL and is not available in MS SQL.
We changed platform last year, and therefore that function can not be used any more.

[sydu]

Thank Geert for this answer.
After many searches I had found this difference between MySQL and SQL MS but i forgot to say it here.
Do you know if it's possible to have the same result with SQL MS? (All my attempts have failed)

[Geert Boedt]

As far as I know, not with PATSTAT Online. The sql query in PATSTAT Online goes first through a parser that "translates" it to SQL.
Maybe you should consider purchasing PATSTAT raw data and install it on a local server. There are user-made functions available on MS SQL that simulate the group-concat function from MySQL.

[sydu]

Once again, thank you Geert.
I will look more closely at the benefits of the paid version.