0

Is it possible in any way (without scripting) to aggregate columns which are stored in a table cell.

Basically what i am trying to achieve here is that if i have the following table

Table 1
KEYNAME    | COLUMN_NAME
COUNT_USER | USER_ID
SUM_HOURS  | HOURS_WATCHED

Can i do something like

select SUM((select column_name from TABLE1 where KEYNAME = "SUM_HOURS")) FROM MAIN_TABLE

I have the option to script in python via which i know how to achieve this. But i am curious to know if this is possible in any version of SQL or not Please let me know if the information is insufficient.

John Rotenstein
  • 241,921
  • 22
  • 380
  • 470
Rhythem Aggarwal
  • 346
  • 2
  • 15
  • I am using Redshift so Postgre – Rhythem Aggarwal Jun 21 '18 at 09:47
  • If I understand correctly, you would have to use dynamic SQL or a giant `case` expression. – Gordon Linoff Jun 21 '18 at 10:29
  • Yes, possible method is to use case. but the thing is that this system is dynamic and will change every month based on client requirements and what metrics they want calculated. So i was thinking to drive it via a config table (not exactly but on the lines above). – Rhythem Aggarwal Jun 21 '18 at 10:37

1 Answers1

2

No, you cannot use a column name that comes from the result of another query.

You would need to do this in your own code, sending the result to Amazon Redshift as a complete query. (That is, run one query to obtain the column names, then run a second query with those column names inserted.)

John Rotenstein
  • 241,921
  • 22
  • 380
  • 470
  • So if i understand correctly, since redshift does not support Stored procedures, i will have to interface the "run one query to obtain the column names, then run a second query with those column names inserted" using some scripting language such as python/unix etc ? – Rhythem Aggarwal Jun 22 '18 at 06:35
  • 2
    Yes, that is correct. Just use Redshift as the "last step" in the process, rather than trying to build too many smarts into the queries. In fact, that is good advice for any system since it avoids being locked-in to any particular technology. I hear of many people wanting to migrate systems out of, say, Oracle but their heavy use of Stored Procedures has locked them into requiring that particular technology. It is better to separate the business logic from the database layer. – John Rotenstein Jun 22 '18 at 07:18
  • Correct that makes perfect sense and answers my curiosity. Thanks!! – Rhythem Aggarwal Jun 22 '18 at 08:59