时间戳

首页 > 解决方案 > 有没有办法将“%”符号添加到我在 Oracle SQL 中的透视结果中并将它们转换为 to_char

sql - 有没有办法将“%”符号添加到我在 Oracle SQL 中的透视结果中并将它们转换为 to_char

问题描述

我正在尝试旋转具有多列的表，但我还想为 1 行附加一个“%”符号。我还需要该行显示 1 个小数点。

原始查询：

select distinct a.TIME_DESC,
round(sum(a.TRX_VOLUMES)) TRx_volume,
round(sum(TARGET_TRX_VOLUMES)) Goal,
round(sum(a.TRX_VOLUMES_LY)) TRx_volume_LY,
round((sum(a.TRX_VOLUMES)/sum(c.TRX_VOLUMES_MKT))*100,1) Mkt_share
from a
join b on a.BRAND_ID=b.BRAND_ID and a.GEO_ID=b.GEO_ID
and a.TIMEPERIODTYPE=b.TIMEPERIODTYPE and a.TIME_DESC=b.TIME_DESC
join c on a.BRAND_ID=c.BRAND_ID and a.GEO_ID=c.GEO_ID
and a.TIMEPERIODTYPE=c.TIMEPERIODTYPE and a.TIME_DESC=c.TIME_DESC
where a.GEO_DESC='BC/YK' and a.type_code='B' and a.dash_brand='Copaxone'
and a.TIMEPERIODTYPE='YEAR'
group by a.TIME_DESC

对于我使用的旋转行

with stg1 as (select distinct a.TIME_DESC,
round(sum(a.TRX_VOLUMES)) TRx_volume,
round(sum(TARGET_TRX_VOLUMES)) Goal,
round(sum(a.TRX_VOLUMES_LY)) TRx_volume_LY,
round((sum(a.TRX_VOLUMES)/sum(c.TRX_VOLUMES_MKT))*100,1) Mkt_share
from a
join b on a.BRAND_ID=b.BRAND_ID and a.GEO_ID=b.GEO_ID
and a.TIMEPERIODTYPE=b.TIMEPERIODTYPE and a.TIME_DESC=b.TIME_DESC
join c on a.BRAND_ID=c.BRAND_ID and a.GEO_ID=c.GEO_ID
and a.TIMEPERIODTYPE=c.TIMEPERIODTYPE and a.TIME_DESC=c.TIME_DESC
where a.GEO_DESC='BC/YK' and a.type_code='B' and a.dash_brand='Copaxone'
and a.TIMEPERIODTYPE='YEAR'
group by a.TIME_DESC),
stg5 AS (SELECT * FROM (select time_desc, Mkt_share from stg1)
pivot
(SUM(Mkt_share) FOR time_desc IN ('Jan 2020', 'Feb 2020', 'Mar 2020', 'Apr 2020', 'May 2020', 'Jun 2020', 'Jul 2020', 'Aug 2020', 'Sep 2020', 'Oct 2020', 'Nov 2020', 'Dec 2020')))
select 'MS %', d.* from stg5 d


'MS%'   'Jan 2020'  'Feb 2020'  'Mar 2020'  
MS %    7           6.5           7.1

我想为 7 添加一个小数，使其成为 7.0 和一个 % 符号。那可能吗？

标签： sqloracleformatappendpivot

解决方案

答案是重新格式化旋转列，例如

TO_CHAR( "Jan 2020",'fm999G990D0','NLS_NUMERIC_CHARACTERS = ''.,''')||'%'

但做更多，通过创建这样的函数将您的查询转换为动态查询：

CREATE OR REPLACE FUNCTION get_brands_volume RETURN SYS_REFCURSOR IS
  recordset SYS_REFCURSOR;
  sqlqry    VARCHAR2(32767);
  cols1     VARCHAR2(32767);
  cols2     VARCHAR2(32767);  
BEGIN
  SELECT LISTAGG(''''||TO_CHAR(TO_DATE(level,'mm'),'Mon yyyy')||''' AS "'||
         TO_CHAR(TO_DATE(level,'mm'),'Mon yyyy')||'"' ,',') 
         WITHIN GROUP (ORDER BY level)
    INTO cols1
    FROM dual
 CONNECT BY level <= 12; 

  SELECT LISTAGG('TO_CHAR( "'||TO_CHAR(TO_DATE(level,'mm'),'Mon yyyy')||'",''fm999G990D0'',
                             ''NLS_NUMERIC_CHARACTERS = ''''.,'''''') AS "'||
                               TO_CHAR(TO_DATE(level,'mm'),'Mon yyyy')||'"',',') 
         WITHIN GROUP (ORDER BY level)
    INTO cols2   
    FROM dual
 CONNECT BY level <= 12;
 
  sqlqry :=
  'WITH stg1  AS
   (SELECT DISTINCT a.TIME_DESC,
                    ROUND(SUM(a.TRX_VOLUMES)) TRx_volume,
                    ROUND(SUM(TARGET_TRX_VOLUMES)) Goal,
                    ROUND(SUM(a.TRX_VOLUMES_LY)) TRx_volume_LY,
                    ROUND((SUM(a.TRX_VOLUMES) / SUM(c.TRX_VOLUMES_MKT)) * 100,1) Mkt_share
      FROM a
      JOIN b
        ON a.BRAND_ID = b.BRAND_ID
       AND a.GEO_ID = b.GEO_ID
       AND a.TIMEPERIODTYPE = b.TIMEPERIODTYPE
       AND a.TIME_DESC = b.TIME_DESC
      JOIN c
        ON a.BRAND_ID = c.BRAND_ID
       AND a.GEO_ID = c.GEO_ID
       AND a.TIMEPERIODTYPE = c.TIMEPERIODTYPE
       AND a.TIME_DESC = c.TIME_DESC
     where a.GEO_DESC = ''BC/YK''
       AND a.type_code = ''B''
       AND a.d ASh_brAND = ''Copaxone''
       AND a.TIMEPERIODTYPE = ''YEAR''
     GROUP BY a.TIME_DESC),
  stg5  AS
   (SELECT '||cols2||'
      FROM stg1
      PIVOT(
             MAX(Mkt_share) FOR time_desc IN('||cols1||')
            )
   )';    

  OPEN recordset FOR sqlqry;
  RETURN recordset;
END;

对于当年的月份，然后从 SQL Developer 的命令行调用

VAR rc REFCURSOR
EXEC :rc := get_brands_volume;
PRINT rc

推荐阅读