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)
GROUP BY id, date
ORDER BY id, date;
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 :
declare rec RECORD;
for rec in SELECT column_name
WHERE table_schema = 'data’
AND table_name = ‘items’
insert into data.test select count(rec) from data.items;
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.