k.gelo k.gelo - 14 days ago 6
SQL Question

Convert Concatenated String to Column Name in Oracle SQL

Problem Statement:

I have a table named ACTIVITIES with all columns having a datatype of varchar(20) shown below:

|ACTIVITY_NAME| Q1 | Q2 | Q3 | Q4 |
|ACT1 |02/05 |05/10 |08/21 |11/15 |
|ACT2 |01/20 |06/11 |08/06 |10/21 |


With this table, I want to get the current quarter based on the system date to select a particular row and convert the quarter data to date. As of now, I have this query but an error is prompting saying that a non-numeric character was found where a numeric was expected which I'm actually familiar of.

select to_date(concat('Q', to_char(sysdate, 'Q')), 'MM/DD')
from activities
where activity_name = 'ACT2';


The result of the concat() function is a string which is shown below that's why there's an error prompt.

select to_date('Q4', 'MM/DD')
from activities
where activity_name = 'ACT2';


Now, I'm seeking for help on how to convert the concatenated string to a Column Name in order for me to extract that specific data on the current quarter and with the specific activity.

Expected Result:

Query:

select to_date(Q4, 'MM/DD')
from activities
where activity_name = 'ACT2';


-- Q4 parameter inside to_date() function is now considered as Column name, not as String. As mentioned on the problem statement above, how can I convert that Q4 string paramater to a column name so I can get the output below.

Output: 08/06/2016

I'm new to oracle sql and I would like to learn from you guys. Really appreciate your help. Thank you in-advance.

Answer

The easiest way is to first unpivot your data:

SELECT *
FROM ACTIVITIES
UNPIVOT (
  Quarter_date FOR qq IN (Q1, Q2, Q3, Q4 )
)
;

You will get a result like this:

ACTI QQ QUARTER_DA
---- -- ----------
ACT1 Q1 02/05     
ACT1 Q2 05/10     
ACT1 Q3 08/21     
ACT1 Q4 11/15     
ACT2 Q1 01/20     
ACT2 Q2 06/11     
ACT2 Q3 08/06     
ACT2 Q4 10/21     

And now picking a row for any quarter is very easy, for example if you need Q4, just add WHERE QQ='Q4' condition:

SELECT *
FROM (
   -- the above query goes here ----
    SELECT * FROM ACTIVITIES
    UNPIVOT (
        Quarter_date FOR qq IN (Q1, Q2, Q3, Q4 )
    )
)
-- replace the below line with:
--    WHERE QQ =  'Q' || to_char(sysdate, 'Q')
-- if you want to get the current quarter based on sysdate
WHERE QQ = 'Q4'
;

ACTI QQ QUARTER_DA
---- -- ----------
ACT1 Q4 11/15     
ACT2 Q4 10/21 

And finaly, to convert a quarter to a date using current year, use:

SELECT x.*
      , to_date( to_char( sysdate, 'yyyy' ) || '/' || QUARTER_DATE, 'yyyy/mm/dd' ) as my_date
FROM (
    SELECT * FROM ACTIVITIES
    UNPIVOT (
        Quarter_date FOR qq IN (Q1, Q2, Q3, Q4 )
    )
) x
WHERE QQ = 'Q4'
;

ACTI QQ QUARTER_DA MY_DATE  
---- -- ---------- ----------
ACT1 Q4 11/15      2016/11/15
ACT2 Q4 10/21      2016/10/21
Comments