Re: Split the result of a query in 2 rows

Frank Pinto wrote
> So:
> If the original result set returned 1 row with 2 columns the new solution
> would return 2 rows with 1 column?
> If the original result set returned 100 row with 3 columns the new
> solution
> would return 300 rows (1 row gets turned into 3 rows * 100 rows = 300)?
>
> I would use unnest
> <http://blog.lerner.co.il/turning-postgresql-arrays-rows-unnest/>.
> Something like this (untested):
>
> WITH temp_table AS (
>   SELECT ARRAY(SELECT field1, field2 FROM tbl_table ORDER BY field1) AS
> prepared_fields;
> )
>   SELECT UNNEST(prepared_fields) FROM temp_table;
>
> Note that's using one query using a CTE (
> http://www.postgresql.org/docs/9.3/static/queries-with.html)
>
> Frank

As noted, I'm pretty sure your query will not work as written but it did
inspire the correct solution:

SELECT unnest(ARRAY[f1, f2])
FROM (VALUES (1,2), (3,4)) f (f1, f2)

Note the difference between: ARRAY[val, val] and ARRAY(subquery)

David J.



--
View this message in context: http://postgresql.nabble.com/Split-the-result-of-a-query-in-2-rows-tp5840497p5840509.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.

--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice