I am trying to retrieve the technical sector and technical field of an application, between 2014-2020, with both inventor(s) and applicant(s) romanian and i want to concatenate the sector and the field to avoid multiple results for the same application.
The problem is that when i concatenate these 2 fields, i get a very long string with the fields concatenated that repeats over and over like this:
Other fields, Civil engineering, Other fields, Civil engineering, Other fields, Civil engineering, Other fields, Civil engineering, Other fields, Civil engineering, Other fields, Civil engineering, Other fields, Civil engineering, Other fields, Civil engineering, Other fields, Civil engineering, Other fields, Civil engineering, Other fields, Civil engineering, Other fields, Civil engineering, Other fields, Civil engineering, Other fields, Civil engineering, Other fields, Civil engineering, Other fields, Civil engineering, Other fields, Civil engineering, Other fields, Civil engineering, Other fields, Civil engineering, Other fields, Civil engineering, Other fields, Civil engineering, Other fields, Civil engineering, Other fields, Civil engineering, Other fields, Civil engineering, Other fields, Civil engineering, Other fields, Civil engineering, Other fields, Civil engineering, Other fields, Civil engineering, Other fields, Civil engineering, Other fields, Civil engineering, Other fields, Civil engineering, Other fields, Civil engineering, Other fields, Civil engineering, Other fields, Civil engineering, Other fields, Civil engineering, Other fields, Civil engineering, Other fields, Civil engineering, Other fields, Civil engineering
My question is how can i avoid creating such a string and get only: Other fields, Civil engineering.
The query i used:
Code: Select all
SELECT distinct appln_auth, appln_nr, appln_filing_date
, inventor.person_ctry_code Inventor_country, applicant.person_ctry_code Applicant_country, applicant.person_name Aplicant_name, string_agg(cast(concat('', tls209_appln_ipc.ipc_class_symbol) as NVARCHAR(MAX)), ', ') Field
FROM tls201_appln join tls207_pers_appln on tls201_appln.appln_id =tls207_pers_appln.appln_id
join tls209_appln_ipc on tls201_appln.appln_id = tls209_appln_ipc.appln_id
join (SELECT distinct tls207_pers_appln.appln_id, tls206_person.person_id, person_name, person_ctry_code
FROM tls206_person join tls207_pers_appln
on tls206_person.person_id = tls207_pers_appln.person_id and applt_seq_nr > 0 ) applicant
on tls201_appln.appln_id = applicant.appln_id
join (SELECT distinct tls207_pers_appln.appln_id, tls206_person.person_id, person_ctry_code
FROM tls206_person join tls207_pers_appln
on tls206_person.person_id = tls207_pers_appln.person_id and invt_seq_nr > 0 ) inventor
on tls201_appln.appln_id = inventor.appln_id
where tls201_appln.appln_filing_year >= '2014' and tls201_appln.appln_filing_year <= '2020' and inventor.person_ctry_code like 'RO' and applicant.person_ctry_code like 'RO' and tls201_appln.granted like 'Y'
group by tls201_appln.appln_nr, tls201_appln.appln_auth, tls201_appln.appln_filing_date, inventor.person_ctry_code, applicant.person_ctry_code, applicant.person_name
order by tls201_appln.appln_filing_date desc