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:
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?
Notes: For the same id
, the arrays will always be the same length and the names will be distinct
Edit: I found another solution using PIVOT
, but I'm still wondering if there are other ways? (Also, what is the most performant?)
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