CODEWITHSUNDEEP
group-bysparqlgroup-concatwikidata
dev2win
dev2win

Reputation: 11

sparql select wikidata group_by and concat

I want to extract a list o players and a list of clubs where it has played, separated by commas.

SELECT DISTINCT ?playerLabel
(GROUP_CONCAT(?teamLabel  ; separator=',') as ?teams)
WHERE {
  ?player wdt:P106 wd:Q937857 .
  
  ?player wdt:P2574 ?team 
  
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
GROUP BY ?playerLabel

I have two problems:

  1. I don't get a list of teams for each player, only the name, and variable ?teams empty.
  2. If I don't use GROUP CONCAT and GROUP BY I obtain the team id, but I prefer the label of team.

For example 2 players...:

playerLabel       teams

Cristiano Ronaldo Sporting Portugal, Manchester U, Real Madrid, Juventus, Manchester U
Leo Messi         Barcelona, PSG

At least I need the Concat and group by, even with code... thanks

Upvotes: 0

Views: 465

Answers (1)

Pascalco
Pascalco

Reputation: 2826

You use P2574, which is "National-Football-Teams.com player ID". While National-Football-Teams.com lists all teams a player played for, this data is not accessible through the Wikidata Query Service. But Wikidata itself has a dedicated property for sports team member: P54. So write ?player wdt:P54 ?team instead of ?player wdt:P2574 ?team.

Additionaly, you need to add ?team rdfs:label ?teamLabel . filter (lang(?teamLabel)='en') to be able to use ?teamLabel in GROUP_CONCAT().

Thus, the full working query looks like this (restricted to US players to avoid query time outs):

SELECT DISTINCT ?playerLabel (GROUP_CONCAT(?teamLabel  ; separator=',') as ?teams)
WHERE {
  ?player wdt:P106 wd:Q937857 .
  ?player wdt:P27 wd:Q30 . 
  ?player wdt:P54 ?team .
  ?team rdfs:label ?teamLabel . filter (lang(?teamLabel)='en')
  SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}
GROUP BY ?playerLabel

Upvotes: 1

Related Questions