Обсуждение: Split the result of a query in 2 rows
I have a very simple query to a single table as follows:
SELECT field1, field2 FROM tbl_table ORDER BY field1
Is it possible to "split" the results so field1 is displayed in one row and field2 in another row?
Best regards,
Jorge Maldonado
JORGE MALDONADO wrote > I have a very simple query to a single table as follows: > > SELECT field1, field2 FROM tbl_table ORDER BY field1 > > Is it possible to "split" the results so field1 is displayed in one row > and > field2 in another row? > > Best regards, > Jorge Maldonado See "UNION ALL" David J. -- View this message in context: http://postgresql.nabble.com/Split-the-result-of-a-query-in-2-rows-tp5840497p5840502.html Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
You can using UNION ALL operator for this task:
SELECT field1 FROM tbl_table
UNION ALL
SELECT field2 FROM tbl_table
ORDER BY 1;
If you do not want duplicates use UNION instead of UNION ALL.
Regards
Yaser
On Wed, Mar 4, 2015 at 1:46 PM, David G Johnston <david.g.johnston@gmail.com> wrote:
JORGE MALDONADO wroteSee "UNION ALL"> I have a very simple query to a single table as follows:
>
> SELECT field1, field2 FROM tbl_table ORDER BY field1
>
> Is it possible to "split" the results so field1 is displayed in one row
> and
> field2 in another row?
>
> Best regards,
> Jorge Maldonado
David J.
--
View this message in context: http://postgresql.nabble.com/Split-the-result-of-a-query-in-2-rows-tp5840497p5840502.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
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. 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
On Wed, Mar 4, 2015 at 12:28 PM, JORGE MALDONADO <jorgemal1960@gmail.com> wrote:
I have a very simple query to a single table as follows:SELECT field1, field2 FROM tbl_table ORDER BY field1Is it possible to "split" the results so field1 is displayed in one row and field2 in another row?Best regards,Jorge Maldonado
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. 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;
I would suggest you test this...
David J.
View this message in context: Re: Split the result of a query in 2 rows
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
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.
Nice, thanks! You've inspired me to have a postgresql Vagrant setup on my windows box to test quickly
Frank
On Wed, Mar 4, 2015 at 1:00 PM, David G Johnston <david.g.johnston@gmail.com> wrote:
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
On Thu, Mar 5, 2015 at 2:28 AM, JORGE MALDONADO <jorgemal1960@gmail.com> wrote:
I have a very simple query to a single table as follows:SELECT field1, field2 FROM tbl_table ORDER BY field1Is it possible to "split" the results so field1 is displayed in one row and field2 in another row?
Considering the order by, maybe this will work:
select x as field 1 from
(SELECT field1 as x, field1 as y FROM tbl_table
UNION ALL
SELECT field2 as x, field2 as y FROM tbl_table) as temp
order by y
select x as field 1 from
(SELECT field1 as x, field1 as y FROM tbl_table
UNION ALL
SELECT field2 as x, field2 as y FROM tbl_table) as temp
order by y
Best regards,Jorge Maldonado
--
Marcos | I love PHP, Linux, and Java