SQL select将行数据显示为varchar项的列

Question

这是我用来生成表的查询：

SELECT cu.idchanneluser AS Approver,
     mcr.idrule AS Rule,
     mrd.idseq AS Seq,
     mcr.idcust AS CustID,
     cu.iduser AS USERID
FROM mstchanneluser cu,
     mstcatruledetail mrd,
     mstcatrule mcr
WHERE idchannel='01'
     AND mrd.idlist=cu.iduser
     AND mrd.idrule=mcr.idrule
     AND mcr.nbrauth='2'
     AND mcr.isautoauth='N'

这是result的快照：

​

​

但是我想要这样的结果：

   FirstAuthorizer SecondAuthorzier Rule    
   rohitcorp        ajitcorp         3090  
   CORPTEST         TESTCORP         8634
   ABHIMAKER        CORPTEST         11705

我尝试了这个查询：

SELECT CASE WHEN idseq = '0' THEN idchanneluser ELSE NULL END AS Approver,
    case when idseq = '1' THEN idchanneluser  ELSE NULL END AS secondApprover,
    cu.idchanneluser AS Approver,
    mcr.idrule AS Rule,
    mrd.idseq AS Seq,
    mcr.idcust AS CustID,
    cu.iduser AS USERID
FROM mstchanneluser cu, 
    mstcatruledetail mrd, 
    mstcatrule mcr 
WHERE idchannel='01'
    AND mrd.idlist=cu.iduser
    AND mrd.idrule=mcr.idrule 
    AND mcr.nbrauth='2'
    AND mcr.isautoauth='N'

然后它会返回给我检查快照的结果。

​

​

Answer 1

这是从您的第二个查询派生而来，您只需要使用MAX并按mcr.idrule对它们进行分组

SELECT MAX(CASE WHEN idseq = '0' THEN idchanneluser ELSE NULL END) AS Approver
    ,  MAX(CASE WHEN idseq = '1' THEN idchanneluser ELSE NULL END) AS secondApprover
    ,  mcr.idrule AS RULE
FROM   mstchanneluser cu
    ,  mstcatruledetail mrd
    ,  mstcatrule mcr
WHERE idchannel = '01'
    AND mrd.idlist = cu.iduser
    AND mrd.idrule = mcr.idrule
    AND mcr.nbrauth = '2'
    AND mcr.isautoauth = 'N'
GROUP BY mcr.idrule

Answer 2

oracle中使用分析函数领先滞后的另一种方法

select tab.y FirstAuthorizer,tab.x SecondAuthorzier,tab.rule from
(
select lead(appprover,0) over (partition by rule order by seq) x ,
lag(appprover,1) over (partition by rule order by seq) y,
rule
from tbl ) tab
where tab.y is not null;