Dynamically choose a column to query in Redshift

Question

I want to take a one-string query response to populate a SELECT statement in another query. Some say it's impossible, but Redshift makes fools of us all.

Imagine I have a table day_of_week as follows:

Day of Week | Weekend
---------------------
  Monday    | No
  Tuesday   | No
  Wednesday | No
  Thursday  | No
  Friday    | No
  Saturday  | Yes
  Sunday    | Yes

And another party_time like this:

Yes          | No
--------------------------
All the time | None of the time

I want to allow someone to just tell me a day (eg, "Wednesday") and then use the resulting Weekend value to query of party_time.

eg

SELECT (SELECT Weekend FROM day_of_week WHERE "Day of Week" = 'Wednesday')
FROM party_time

Result: 'None of the time'

How?

Perhaps there is some clever technique using UNPIVOT to turn column names into values which could work for you, but that is beyond my capabilities ATM. — Turophile
– Turophile, Commented Oct 22, 2015 at 2:42

Turophile · Accepted Answer · 2015-10-22 02:40:00Z

1

SQL itself isn't dynamic/self-referential although most implementations have some sort of meta language to partly get around that.

The most obvious solution for your problem is to change table party_time:

Test    Meaning
--------------------------
Yes     All the time
No      None of the time

Then you can use a join or a sub-select to get to your answer:

select meaning
from party_time
inner join day_of_week
on weekend = test
where Day_of_Week = 'Wednesday'

Example: http://sqlfiddle.com/#!9/f10b7/1

answered Oct 22, 2015 at 2:40

Turophile

3,4151 gold badge15 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ScottieB Over a year ago

Yeah, agreed. I was trying to make a toy example. In reality, I have a table that has four columns: index + 3 stats for hundreds of objects. I have a user interface that can pick which stat to display. So I really just want to pass that variable to query and only pull the relevant stat.

Collectives™ on Stack Overflow

Dynamically choose a column to query in Redshift

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related