Home > mailing lists

Thread: Split the result of a query in 2 rows

Split the result of a query in 2 rows

From

JORGE MALDONADO

Date:

04 March 2015, 21:28:30

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

Re: Split the result of a query in 2 rows

From

David G Johnston

Date:

04 March 2015, 21:46:52

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.

Re: Split the result of a query in 2 rows

From

Yaser Raja

Date:

04 March 2015, 21:50:03

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 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.

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

Re: Split the result of a query in 2 rows

From

Frank Pinto

Date:

04 March 2015, 21:53:08

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 field1

Is it possible to "split" the results so field1 is displayed in one row and field2 in another row?

Best regards,
Jorge Maldonado

Re: Split the result of a query in 2 rows

From

David G Johnston

Date:

04 March 2015, 21:56:20

On Wed, Mar 4, 2015 at 11:53 AM, Frank Pinto [via PostgreSQL] <[hidden email]> 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. 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.

Re: Split the result of a query in 2 rows

From

David G Johnston

Date:

04 March 2015, 22:00:56

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.

Re: Split the result of a query in 2 rows

From

Frank Pinto

Date:

04 March 2015, 22:06:45

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

Re: Split the result of a query in 2 rows

From

Marcos Almeida Azevedo

Date:

05 March 2015, 04:34:36

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 field1

Is 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

Best regards,
Jorge Maldonado

Marcos | I love PHP, Linux, and Java