SPARQL combine - concatenate two columns into one

Question

I'm a newbie to SPARQL, and I would like to combine two columns into one

initial table

a|b|1
c|d|2

wanted table

a|b
c|d
b|1
d|2

its like creating two different tables and putting them one on the other.

I need that to make a visualisation using d3sparql which takes this data form, I also though about importing a json and modifying it then printing it to the screen, but if this is possible that makes things a lot easier and faster..

UPDATE: My original Query looks like that

PREFIX prefix1:<...>
PREFIX prefix2:<...>
SELECT ?x ?y ?z 
WHERE  { 
?x prefix1:OwnsY ?y.
?y prefix1:PublishesZ ?z.
}

What was your original query? I think you just need to split the original into a union, but it's a bit hard to help when you present it like this. — user205512, Mar 09 '15 at 14:26
@ozama does your dataset contain any uses of owns or publishes there the other components aren't present? That is, do you just want to select all the owns and publishes triples, or do you only want ?x and ?z when there is a *common* y between them? — Joshua Taylor, Mar 09 '15 at 15:13
@Joshua Yes mister, what i want to achieve requires all the owns and publishes in one raw, — Oussama L., Mar 09 '15 at 15:18
@ozama So if "a owns b" but there's no c such that "b owns c", you still want "a b" in the output? If that's the case, then your query doesn't actually achieve that, but you can use the approach in [my answer](http://stackoverflow.com/a/28945157/1281433). Be sure to read the comments to understand the difference between the two approaches. — Joshua Taylor, Mar 09 '15 at 15:29

user205512 · Accepted Answer · 2015-03-09T15:13:05.117

2

The simplest way (but not the most efficient) is:

PREFIX prefix1:<...>
PREFIX prefix2:<...>
SELECT DISTINCT ?x ?z 
WHERE  {
    { ?x prefix1:OwnsY ?y. ?y prefix1:PublishesZ ?z. }
    UNION
    { ?x prefix1:OwnsY ?z. ?z prefix1:PublishesZ ?whatever. }
}

which in effect performs the query twice.

edited Mar 09 '15 at 15:13

answered Mar 09 '15 at 15:02

user205512

8,798
29
28

This will have get the results that the original query retrieved, but based on [ozama's comment](http://stackoverflow.com/questions/28943956/sparql-combine-concatenate-two-columns-into-one?noredirect=1#comment46145119_28943956), it sounds like the original query may actually be too restrictive. – Joshua Taylor Mar 09 '15 at 15:33

score 1 · Answer 2 · edited May 23 '17 at 12:24

If you're just trying to extract all the subjects and objects of the owns and published triples, then this can be a very simple query. If, on the other hand, you only want data where there's all three parts, you'll need a full union. Let's create some example data and see what we can do:

@prefix : <urn:ex:>

:a :owns :b .
:b :publishes 1 .

:c :owns :d .
:d :publishes 2 .

:e :owns :f .  # no corresponding :publishes

:g :publishes :h .  # no corresponding :ownsprefix : <urn:ex:>

Here's your current query and results:

select ?x ?y ?z {
  ?x :owns ?y .
  ?y :publishes ?z .
}

---------------
| x  | y  | z |
===============
| :a | :b | 1 |
| :c | :d | 2 |
---------------

Now, if you're willing to get those owns and publishes triples that don't have corresponding publishes and owns triples, you can use a union, values block, or property path. The union would be

{ ?x :owns ?y } union { ?x :publishes ?y }

The values block would would be a bit simpler:

values ?p { :owns :publishes }
?x ?p ?y

You can make that even simpler with a property path:

?x :owns|:publishes ?y

These all give you:

-----------
| x  | y  |
===========
| :a | :b |
| :b | 1  |
| :c | :d |
| :d | 2  |
| :e | :f | * owns
| :g | :h | * publishes
-----------

Note that the rows with stars are present because "e owns f" and "g publishes h", even though there's nothing that f publishes, and nothing that owns g. Your original query won't find those. If you need to exclude the owns and publishes triples that don't have publishes and own counterparts, you'll need to use the union option, that includes all the parts as in user205512's answer.

This isn't equivalent. The query requires that x owns y *and* y publishes z, whereas you've used an or. — user205512, Mar 09 '15 at 15:10
That's a very good point; this will pick up owned things that haven't published anything, and published things that aren't owned. — Joshua Taylor, Mar 09 '15 at 15:11
@user205512 I've updated my answer to make that explicit; this only works if the point is to extract *both* types of triples, rather than to extract the more complex structure. — Joshua Taylor, Mar 09 '15 at 15:28
Yes, it's a bit ambiguous. I think I can nearly do this without needing to know the original query -- you ought to be able to take the results of the sub-selects, do a cross product to double up the rows, and project / bind accordingly. Crazy, though :-) — user205512, Mar 09 '15 at 16:15

SPARQL combine - concatenate two columns into one

2 Answers2