CODEWITHSUNDEEP
sqloracle-database
szakwani
szakwani

Reputation: 384

Dynamic Column Alias Name

I am looking for a way where I could set the column alias name as dynamic DDL

CREATE TABLE PRODUCTS
(
PRODUCT_NAME VARCHAR(20),
PRODUCT_PRICE INT );


INSERT INTO PRODUCTS VALUES ('COKE',20);
INSERT INTO PRODUCTS VALUES ('PEPSI',10);
INSERT INTO PRODUCTS VALUES ('FANTA',30);
INSERT INTO PRODUCTS VALUES ('COKE',30);

NOW

SELECT SUM(DECODE(PRODUCTS.PRODUCT_NAME,'COKE',PRODUCTS.PRODUCT_PRICE)) AS SSS
FROM PRODUCTS

would result in 

SSS
50

and

SELECT PRODUCT_NAME FROM (SELECT PRODUCT_NAME, ROWNUM AS RANK FROM PRODUCTS 
WHERE ROWNUM = 1)

results in

COKE

I would like to replace sss with coke but from a dynamic perspective that if the value is changed in the database so would the alias

SELECT SUM(DECODE(PRODUCTS.PRODUCT_NAME,'COKE',PRODUCTS.PRODUCT_PRICE)) AS
(
   SELECT PRODUCT_NAME FROM 
   (SELECT PRODUCT_NAME, ROWNUM AS RANK FROM PRODUCTS WHERE ROWNUM = 1)
)
FROM PRODUCTS

Upvotes: 1

Views: 7092

Answers (2)

psaraj12
psaraj12

Reputation: 5072

You can use PIVOT to achieve the same.

If you want to use a sub-query instead of values for Product_name use PIVOT XML(Note the result will be in XML in this case)

For more information please check the below link

PIVOT and UNPIVOT operators in Oracle 11g

with prod as (SELECT PRODUCT_NAME, product_price FROM PRODUCTS)
     select *
     from  prod
     pivot  (sum(product_price) 
            for (product_name) in
            ('COKE' as COKE
          , 'FANTA' as FANTA
          , 'PEPSI' as PEPSI ))

EDIT 1:- You can also select only a particular drink like below

     with prod as (SELECT PRODUCT_NAME, product_price FROM PRODUCTS)
     select COKE
     from  prod
     pivot  (sum(product_price) 
            for (product_name) in
            ('COKE' as COKE
          , 'FANTA' as FANTA
          , 'PEPSI' as PEPSI ))

Upvotes: 2

Ajay2707
Ajay2707

Reputation: 5798

You have to use dynamic query means you create a dynamic statement and then execute.

Here is an example, use if else for this.

For your query change like this.

declare
  l_cursor sys_refcursor;
  sql_query varchar2(4000);
begin
  sql_query := 'select 1 as value from dual';
  open l_cursor for sql_query;
end;

http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/dynamic.htm#LNPLS01102

Upvotes: 0

Related Questions