Given a table with this schema:
| id | name | values |
|---|---|---|
| 1 | a | [1,2,3] |
| 1 | b | [4,5,6] |
| 1 | c | [x,x,y] |
Can I query it to receive this:
| id | a | b | c |
|---|---|---|---|
| 1 | 1 | 4 | x |
| 1 | 2 | 5 | x |
| 1 | 3 | 6 | y |
And be then able to filter e.g.
WHERE c = 'x'
or
WHERE b >= 4 AND b < 6
One way I found is this:
SELECT t1.id, t1.v as a, t2.v as b FROM
(SELECT id, v, index
FROM
table as t,
t.values AS v AT index
WHERE t.name = 'a') as t1
JOIN
(SELECT id, v, index
FROM
table as t,
t.values AS v AT index
WHERE t.name = 'b') as t2
ON t1.index = t2.index
But this is cumbersome if I want to combine an arbitrary number of rows (and slow maybe?). Is there a better way?
Edit:
Found this way working with PIVOT. That seems also a viable solution, but are there others?
SELECT * FROM
(SELECT id, name, v, index
FROM
table as t,
t.values AS v AT index
WHERE id = 0 and name IN ('a', 'b', 'c')) AS t PIVOT (max(v) FOR name IN ('a', 'b', 'c'))
ORDER BY index