Add columns dynamically in the result of a query. Oracle

Question

I have a query that retrieve two columns.

Like this:

select subscription_id, months from subscriptions;

Now, the result is like this:

subscription_id  months
      1254         4
      5896         1
      8965         3

I want to add in the result of the query, in the right side, columns dynamically, depends of the number of months that are retrieved. If the result for one record is 4, I'll add for the next 4 columns the value 4 over the same row and so on.

Example:

subscription_id  months    0     1    2    3    4    5
      1254         4       4     4    4    4    4
      5896         1       1     1
      8965         3       3     3    3    3    3

NOTE: I'm using Oracle 10g, pivot is not possible to use.

Khristian Liahut · Accepted Answer · 2014-02-14 10:58:11Z

One way to do this:

select 
       subscription_id,
       months,
       CASE WHEN MONTHS >= 0 THEN MONTHS END Month_0,
       CASE WHEN MONTHS >= 1 THEN MONTHS END Month_1,
       CASE WHEN MONTHS >= 2 THEN MONTHS END Month_2,
       CASE WHEN MONTHS >= 3 THEN MONTHS END Month_3,
       CASE WHEN MONTHS >= 4 THEN MONTHS END Month_4,
       CASE WHEN MONTHS >= 5 THEN MONTHS END Month_5,
       CASE WHEN MONTHS >= 6 THEN MONTHS END Month_6,
       CASE WHEN MONTHS >= 7 THEN MONTHS END Month_7,
       CASE WHEN MONTHS >= 8 THEN MONTHS END Month_8,
       CASE WHEN MONTHS >= 9 THEN MONTHS END Month_9,                                                       
       CASE WHEN MONTHS >= 10 THEN MONTHS END Month_10,
       CASE WHEN MONTHS >= 11 THEN MONTHS END Month_11,
       CASE WHEN MONTHS >= 12 THEN MONTHS END Month_12 
from subscriptions;

Collectives™ on Stack Overflow

Add columns dynamically in the result of a query. Oracle

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related