时间戳

首页 > 解决方案 > 在 postgres 12 中枢轴

postgresql - 在 postgres 12 中枢轴

问题描述

我在 sql 中使用以下查询。

SELECT * FROM (SELECT DLHSTS FROM DLHHDR A,rtlgen rtl  WHERE 1 =1  AND rtl.rtlid= a.dlhrtlid 
AND
rtl.rtltype = DECODE(
(SELECT pmvnum1 FROM prmval WHERE pmvpmlno = 100224 AND pmvno = 1 ),
1,
(select p.PMVNAME from PRMVAL p where p.PMVPMLNO=100251 and p.PMVNO=2),
(select p.PMVNAME from PRMVAL p where p.PMVPMLNO=100251 and p.PMVNO=1)) 
and 
DLHSTS NOT IN ('AV')
and  (to_char(dlhvdrid) in (select uatval   from usratt   where uatusrid = 2   and uatuapid = 1)
or
exists  (select 1  from dlditm dld, itmext, usratt   where dlditmid = itxrootxtno   
and dlhid = dld.dldhdrid 
and itxbmid = REGEXP_SUBSTR(UATVAL, '[^|]+', 1, 1)
and (itxdptid = REGEXP_SUBSTR(UATVAL, '[^|]+', 1, 2)
or
REGEXP_SUBSTR(UATVAL, '[^|]+', 1, 2) = '0')
and UATUSRID = 2  
and uatuapid = 2)) )
PIVOT ( COUNT(DLHSTS) FOR DLHSTS IN ('NS', 'SB', 'TV', 'AP', 'AE', 'TR', 'NI', 'RJ','CN','TE'))

如何在 Postgres 中转换此枢轴。我尝试使用 tablefunc 但我不知道如何在这里使用它。

标签： postgresql

解决方案

我已将查询转换为 postgres。

SELECT
COUNT(CASE WHEN DLHSTS = 'NS' THEN 1 END) AS "'NS'"
,COUNT(CASE WHEN DLHSTS= 'SB' THEN 1 END) AS "'SB'"
,COUNT(CASE WHEN DLHSTS = 'TV' THEN 1 END) AS "'TV'"
,COUNT(CASE WHEN DLHSTS = 'AP' THEN 1 END) AS "'AP'"
,COUNT(CASE WHEN DLHSTS = 'AE' THEN 1 END) AS "'AE'"
,COUNT(CASE WHEN DLHSTS = 'TR' THEN 1 END) AS "'TR'"
,COUNT(CASE WHEN DLHSTS = 'NI' THEN 1 END) AS "'NI'"
,COUNT(CASE WHEN DLHSTS = 'RJ' THEN 1 END) AS "'RJ'"
,COUNT(CASE WHEN DLHSTS = 'CN' THEN 1 END) AS "'CN'"
,COUNT(CASE WHEN DLHSTS = 'TE' THEN 1 END) AS "'TE'"
FROM
(select * from (SELECT DLHSTS FROM USMQA.DLHHDR A,USMQA.rtlgen rtl  WHERE 1 =1
 AND rtl.rtlid= a.dlhrtlid  
 AND rtl.rtltype = (CASE WHEN(SELECT pmvnum1 FROM USMQA.prmval WHERE pmvpmlno = 100224 
 AND pmvno = 1 ) = 1
 THEN (select p.PMVNAME from USMQA.PRMVAL p where p.PMVPMLNO=100251 and p.PMVNO=2) 
 ELSE (select p.PMVNAME from USMQA.PRMVAL p where p.PMVPMLNO=100251 and p.PMVNO=1) 
 END) 
 and DLHSTS NOT IN ('AV')
 and (to_char(dlhvdrid,'FM99999999') in
 (select uatval   from USMQA.usratt   where uatusrid = 2   and uatuapid = 1) or 
 exists  
  (select 1  from USMQA.dlditm dld, USMQA.itmext, USMQA.usratt   where 
  dlditmid = itxrootxtno   
  and dlhid = dld.dldhdrid
    and itxbmid::CHAR = split_part(UATVAL, '|', 1)  
    and (itxdptid::CHAR = split_part(UATVAL, '|',  2) 
     or         split_part(UATVAL, '|', 2) = '0')
    and UATUSRID = 2   and uatuapid = 2))) as foo) as t;

推荐阅读