How to select 200 columns in one SQL query



  • Is there a way to select a lot of variables based on looping through a list?

    The table A that I have is
    id, date, attr1, …,attr200
    1 , 1, 0.51, … ,0.38
    1 , 3, 0.21, … ,0.44
    1 , 1, .023, … ,0.48
    2 , 1, 0.88, … ,0.33
    3 , 5, 0.31, … ,0.21

    I would like to to sum attr1, attr2, …, attr200 separately group by id and date without explicitly using the following query.

    SELECT id, date, SUM(attr1), SUM(attr2), …, SUM(attr200)
    FROM A
    GROUP BY id, date
    ORDER BY id, date;

    Any ideas?



  • You can implement ‘for loop’ in postgres sql. However it’s not as easy as just write a for loop in a select statement because loops are not part of the SQL language.

    You can use this as an example:

    First, create a table to hold the results:
    create table data.test (result int);

    Use DO and a for loop to inserting the results :
    DO $$
    declare rec RECORD;
    begin
    for rec in SELECT column_name
    FROM information_schema.columns
    WHERE table_schema = 'data’
    AND table_name = ‘items’

    loop
    insert into data.test select count(rec) from data.items;

    end loop;
    end $$

    Loops is part of control structures (https://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-CONTROL-STRUCTURES-LOOPS) and it’s pretty advanced. In my experience, I used more of bash script or python connection to run this.


登录后回复
 

与 BitTiger Community 的连接断开,我们正在尝试重连,请耐心等待