How to display my query result vertically in postgresql?

Question

This is my SQL query and i want these results to be displayed vertically. I also searched google for this where i found to turn the toggle mode on by using \x\g\x but i don,t know where to put that syntax. Please help to get output like this:

enter image description here

But, my this query gives output like this:

enter image description here

                select
            round(
            100.00 *
            (sum(case when "WELL_AGE" <= '5' AND "CONC_ARSC" <= '10' then 1 else 0 end))/(sum(case when "WELL_AGE" <= '5' then 1 else 0 end)),1) conc_arscbelow5_wellageGrp,

            round(
            100.00 *
            (sum(case when "WELL_AGE" >= '6' AND "WELL_AGE" <= '10' AND "CONC_ARSC" <= '10' then 1 else 0 end))/(sum(case when "WELL_AGE" >= '6' AND "WELL_AGE" <= '10' then 1 else 0 end)),1) conc_arscbet6_10wellageGrp,

            round(
            100.00 *
            (sum(case when "WELL_AGE" >= '11' AND "WELL_AGE" <= '15' AND "CONC_ARSC" <= '10' then 1 else 0 end))/(sum(case when "WELL_AGE" >= '11' AND "WELL_AGE" <= '15' then 1 else 0 end)),1) conc_arscbet11_15_wellageGrp,

            round(
            100.00 *
            (sum(case when "WELL_AGE" >= '16' AND "WELL_AGE" <= '30' AND "CONC_ARSC" <= '10' then 1 else 0 end))/(sum(case when "WELL_AGE" >= '16' AND "WELL_AGE" <= '30' then 1 else 0 end)),1) conc_arscbet16_30wellageGrp,

            round(
            100.00 *
            (sum(case when "WELL_AGE" >= '31' AND "WELL_AGE" <= '50' AND "CONC_ARSC" <= '10' then 1 else 0 end))/(sum(case when "WELL_AGE" >= '31' AND "WELL_AGE" <= '50' then 1 else 0 end)),1) conc_arscbet31_50wellageGrp,

            round(
            100.00 *
            (sum(case when "WELL_AGE" > '50' AND  "CONC_ARSC" <= '10' then 1 else 0 end))/(sum(case when "WELL_AGE" > '50' then 1 else 0 end)),1 )conc_arscabove50_wellageGrp


            from public."Arsenic_Test";

See this http://stackoverflow.com/questions/23060256/postgres-transpose-rows-to-columns — Pramod CS, Sep 07 '16 at 09:04
The `\x` command is only valid for the command line client `psql` and it will simply "rotate" the output of any SQL query. There is no `psql` metacommand `\x\g\x`. `\g` will run the statement from the edit buffer. — , Sep 07 '16 at 12:30

Mabu Kloesen · Answer 1 · 2017-06-29T06:36:03.417

I hope that this will match with your requirement.

Using hstore:

with t as (SELECT '80.13' aS "0-5", '80.7' AS "6-10", '81.6' AS "11-15", '84.27' AS "16-30", '84.04' AS "31-50", '85.33' AS ">50")
SELECT * 
FROM (SELECT (each(hstore(t))).* FROM t) AS tmp (well_age, below10_conc_arsc_wells)

Replace t with your table. Using json:

with t as (SELECT '80.13' aS "0-5", '80.7' AS "6-10", '81.6' AS "11-15", '84.27' AS "16-30", '84.04' AS "31-50", '85.33' AS ">50")
SELECT * 
FROM (SELECT (json_each_text(row_to_json(t))).* FROM t) AS tmp (well_age, below10_conc_arsc_wells)

score 0 · Answer 2 · answered Jun 29 '17 at 13:16

Use the range type to group on:

with r (r,s) as ( values
    (int4range(null,5,'[]'), '0 - 5'),
    (int4range(6,10,'[]'), '6 - 10'),
    (int4range(11,15,'[]'), '11 - 15'),
    (int4range(16,30,'[]'), '16 - 30'),
    (int4range(31,50,'[]'), '31 - 50'),
    (int4range(50,null,'(]'), '>50')
)
select s, 100* count("CONC_ARSC" <= '10' or null) / count(*)
from
    public."Arsenic_Test"
    inner join
    r on "WELL_AGE" <@ r
group by s;

How to display my query result vertically in postgresql?

2 Answers2