动态列别名 [英] Dynamic Column Alias Name

问题描述

我正在寻找一种方法，可以将列别名设置为动态 DDL

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);

现在

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

将导致

SSS
50

和

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

产生

COKE

我想用可乐代替sss，但是从动态的角度来看，如果数据库中的值改变了，别名也会改变

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

推荐答案

您可以使用PIVOT来实现相同的目的.

You can use PIVOT to achieve the same.

如果要使用子查询代替Product_name的值 使用PIVOT XML(请注意，在这种情况下，结果将为XML)

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

Oracle 11g中的PIVOT和UNPIVOT运算符

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 ))

这篇关于动态列别名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！