CODEWITHSUNDEEP
oracle-databaseplsqloracle10goracle-sqldeveloper
Shubh
Shubh

Reputation: 17

How to column value as column name dynamicaly in oracle plsql query

I need specific value of column as column name not to conversion row to column by using following query with result

Blockquote

 SELECT AAA.fin_year_quarter,AAA.GEOG_STATE_NAME,AAA.SKU_NAME,SUM(AAA.VALUE) 
     FROM (
              SELECT DISTINCT dm.fin_year_quarter,FCPS.TERRITORY_SID,
                               DG.GEOG_STATE_NAME,dp.sku_name,
                               SUM(FCPS.PRIMARY_SALES_VALUE) AS VALUE
               FROM fact_chw_primary_sales FCPS,DIM_GEOGRAPHY
                     DG,dim_productdp,dim_month dm
               WHERE FCPS.TERRITORY_SID=DG.TERRITORY_SID
               and  fcps.product_sid=dp.product_sid
               and fcps.month_sid=dm.month_sid
               AND DM.MONTH_SID=5845
               GROUP BY FCPS.TERRITORY_SID,DG.GEOG_STATE_NAME,  
                       dp.sku_name,dm.fin_year_quarter
             )AAA
      GROUP BY AAA.GEOG_STATE_NAME,AAA.SKU_NAME,AAA.fin_year_quarter
      ORDER BY AAA.GEOG_STATE_NAME ASC, SUM(AAA.VALUE) DESC,AAA.SKU_NAME ASC;

The result of this query is :-

        FIN_YEAR_QUARTER GEOG_STATE_NAME   SKU_NAME    **SUM(AAA.VALUE)**
      **2015-2016-Q4**   DELHI-0801        PVPM0017G    4195295
        2015-2016-Q4     DELHI-0801        RGPRM035G    3191880
        2015-2016-Q4     DELHI-0801        RGPM0035G    1397599
        2015-2016-Q4     DELHI-0801        PVPR0017G    250369.5
        2015-2016-Q4     DELHI-0801        PVPM0180G    65248.22
        2015-2016-Q4     DELHI-0801        PVPM1000G    42258.18
        2015-2016-Q4     DELHI-0801        PVPS0380G    9272.4

But My requirement is like that as with above query :- 

        FIN_YEAR_QUARTER GEOG_STATE_NAME   SKU_NAME   **2015-2016-Q4**
      **2015-2016-Q4**   DELHI-0801        PVPM0017G    4195295
        2015-2016-Q4     DELHI-0801        RGPRM035G    3191880
        2015-2016-Q4     DELHI-0801        RGPM0035G    1397599
        2015-2016-Q4     DELHI-0801        PVPR0017G    250369.5
        2015-2016-Q4     DELHI-0801        PVPM0180G    65248.22
        2015-2016-Q4     DELHI-0801        PVPM1000G    42258.18
        2015-2016-Q4     DELHI-0801        PVPS0380G    9272.4

For quarter 3rd result will be as :- 

 "FIN_YEAR_QUARTER" "GEOG_STATE_NAME"  "SKU_NAME"   **"2015-2016-Q3"**

  **2015-2016-Q3**   BOMBAY-0802      PVPM00173G  5195292
    2015-2016-Q3     BOMBAY-0802        RGPRM0353G    4191881
    2015-2016-Q3     BOMBAY-0802        RGPM00353G    39759
    2015-2016-Q3     BOMBAY-0802        PVPR00173G    2503.5
    2015-2016-Q3     BOMBAY-0802        PVPM01803G    652.22
    2015-2016-Q3     BOMBAY-0802        PVPM10003G    14225.18
    2015-2016-Q3     BOMBAY-0802        PVPS03803G    927200.4

This Result are coming the bases of DM.MONTH_SID please suggest Its need full.

Upvotes: 0

Views: 3311

Answers (2)

mahi_0707
mahi_0707

Reputation: 1062

Firstly, you can't have 2015-2016-Q4 as column name. Since it is starting with number and contains hyphens in between, you will encounter the below error :

    ORA-00923: FROM keyword not found where expected

However, you can use column name as Q4_2015_2016

Try Something like this using Dynamic SQL - 

     DECLARE 
     sql_query varchar(400);
     Var1 varchar2(20);
     BEGIN
     select  distinct 
     (case  FIN_YEAR_QUARTER when '2015-2016-Q4' then 'Q4_2015_2016' else 'NOT Q4' end )
     into Var1 
     from SALES;
     sql_query := ' select GEOG_STATE_NAME,FIN_YEAR_QUARTER,value as  ' || var1 || '
                    from SALES ' ; 
    dbms_output.put_line('Generated SQL is  =>  ' ||chr(10)||chr(13)|| sql_query);
    END;

Pl/sql Block Output:

DBMS_OUTPUT

output of the query generated above:

OUTPUT

PS: Replace your query in the SQL_QUERY

Upvotes: 2

jmiracle
jmiracle

Reputation: 1

I think this depends on the end user tool. If you are using Oracle BI-publisher you can group the data as per 1st column. And then use the value in the 1st column value as heading for the 4th.

Upvotes: 0

Related Questions