需要从 select 语句中按列划分的行数据

Question

需要客户表中的逐行数据到列数据中。我需要一个选择语句或函数来做到这一点。

账户功能表

Client  Acct    Function    Value
OK  OK123   Is_Human    Y
OK  OK144   Is_Human    N
OK  OK123   Is_Live Y
OK  OK144   Is_Live Y
VV  VV553   Is_Human    N
VV  VV510   Is_Human    Y

账户表

Client  Acct    Acct_name
OK  OK123   OK123_Pnb
OK  OK144   OK144_Bnb
VV  VV553   VV553_Vnb
VV  VV510   VV510_Vpk

我尝试使用listagg，但无法达到预期的结果

select * from Account where client=OK
Expected output
Client  Acct    Acct_name Is_Human Is_Human_value Is_Live Is_live_value
OK  OK123   OK123_Pnb Y    Yes            Y   Active
OK  OK144   OK144_Bnb N    No             N   Inactive
select * from Account where client=VV
Expected output
Client  Acct    Acct_name Is_Human Is_Human_value Is_Live Is_live_value
VV  VV553   VV553_Vnb Y    Yes      
VV  VV510   VV510_Vpk N    No

Answer 1

您可以使用以下查询：

SELECT
    CLIENT,
    ACCT,
    ACCT_NAME,
    CASE
        WHEN IS_HUMAN IS NULL THEN 'N'
        ELSE IS_HUMAN
    END AS IS_HUMAN,
    CASE
        WHEN IS_HUMAN = 'Y' THEN 'Yes'
        ELSE 'No'
    END AS IS_HUMAN_VALUE,
    CASE
        WHEN IS_LIVE IS NULL THEN 'N'
        ELSE IS_LIVE
    END AS IS_LIVE,
    CASE
        WHEN IS_LIVE = 'Y' THEN 'Yes'
        ELSE 'No'
    END AS IS_LIVE_VALUE
FROM
    (
        SELECT
            A.CLIENT,
            A.ACCT,
            A.ACCT_NAME,
            MAX(CASE
                WHEN AF.FUNCTION = 'IS_HUMAN' THEN AF.VALUE
            END) AS IS_HUMAN,
            MAX(CASE
                WHEN AF.FUNCTION = 'IS_LIVE' THEN AF.VALUE
            END) AS IS_LIVE
        FROM
            ACCOUNT A
            LEFT JOIN ACC_FUNCTION AF ON AF.CLIENT = A.CLIENT
                                         AND A.ACCT = AF.ACCT
        GROUP BY
            A.CLIENT,
            A.ACCT,
            A.ACCT_NAME
    )

我为此创建了一个演示。

您可以根据需要添加condition of the client column。

希望，这对你有用。

干杯！！

Answer 2

你想要透视两个值。添加两次case以所需格式显示输出：

select client, acct, acct_name, 
       is_human, case is_human when 'Y' then 'Yes' when 'N' then 'No' end is_human_value,
       is_live, case is_live when 'Y' then 'Active' when 'N' then 'Inactive' end is_live_value
  from account a join account_function using (client, acct)
  pivot (max(value) for function in ('Is_Human' is_human, 'Is_Live' is_live))
  order by acct;

结果：

CLIENT ACCT  ACCT_NAME IS_HUMAN IS_HUMAN_VALUE IS_LIVE IS_LIVE_VALUE
------ ----- --------- -------- -------------- ------- -------------
OK     OK123 OK123_Pnb Y        Yes            Y       Active
OK     OK144 OK144_Bnb N        No             Y       Active
VV     VV510 VV510_Vpk Y        Yes                    
VV     VV553 VV553_Vnb N        No 

^演示

Answer 3

可以使用以下过程创建动态视图：

CREATE OR REPLACE PROCEDURE PROCESS_VW_DATA
IS
   VVIEWCOLUMN   VARCHAR2 (2000) DEFAULT '' ;
   SQL_STMT1     VARCHAR2 (2000);
BEGIN
   BEGIN
      FOR C IN (  SELECT   DISTINCT FUNCT
                    FROM   ACCOUNT_FUNCTION
                ORDER BY   1)
      LOOP
         VVIEWCOLUMN :=
               VVIEWCOLUMN
            || ' '
            || 'MAX (DECODE (FUNCT, '
            || ''''
            || C.FUNCT
            || ''''
            || ', RESULT)) AS '
            || C.FUNCT
            || ', '
            || 'CASE WHEN MAX (DECODE (FUNCT, '
            || ''''
            || C.FUNCT
            || ''''
            || ', RESULT)) = ''Y'' AND UPPER('
            || ''''
            || C.FUNCT
            || ''''
            || ') = ''IS_LIVE'' THEN ''Active''  
                 WHEN COALESCE(MAX (DECODE (FUNCT, '
            || ''''
            || C.FUNCT
            || ''''
            || ', RESULT)),''N'') = ''N'' AND UPPER('
            || ''''
            || C.FUNCT
            || ''''
            || ') = ''IS_LIVE'' THEN ''Inactive'' 
                 WHEN MAX (DECODE (FUNCT, '
            || ''''
            || C.FUNCT
            || ''''
            || ', RESULT)) = ''Y'' AND UPPER('
            || ''''
            || C.FUNCT
            || ''''
            || ') <> ''IS_LIVE'' THEN ''Yes'' 
                ELSE ''No'' END AS '
            || C.FUNCT
            || '_VALUE'
            || ',';
      END LOOP;
      SELECT   DISTINCT SUBSTR (VVIEWCOLUMN, 1, LENGTH (VVIEWCOLUMN) - 1)
        INTO   VVIEWCOLUMN
        FROM   DUAL;
   END;
   BEGIN
      SQL_STMT1 :=
         ' CREATE OR REPLACE VIEW TEST_VIEW AS 
                  SELECT   AF.CLIENT,
                           AF.ACCT,
                           ACT.ACCT_NAME,'
         || VVIEWCOLUMN
         || '
                    FROM   ACCOUNT_FUNCTION AF, ACCOUNT ACT
                   WHERE   AF.ACCT = ACT.ACCT
                   GROUP BY   AF.ACCT, AF.CLIENT, ACT.ACCT_NAME
                ORDER BY   1';
      EXECUTE IMMEDIATE SQL_STMT1;
   END;
END PROCESS_VW_DATA;

然后执行该过程：

EXEC PROCESS_VW_DATA

现在，您可以运行所需的查询：

SELECT * FROM TEST_VIEW WHERE CLIENT='OK'

如果要在查询期间将"活动/非活动"设置为状态，请替换过程的以下部分

 VVIEWCOLUMN :=
       VVIEWCOLUMN
    || ' '
    || 'MAX (DECODE (FUNCT, '
    || ''''
    || C.FUNCT
    || ''''
    || ', RESULT)) AS '
    || C.FUNCT
    || ','
    || 'CASE WHEN MAX (DECODE (FUNCT, '
    || ''''
    || C.FUNCT
    || ''''
    || ', RESULT)) = ''Y'' THEN ''Yes'' ELSE ''No'' END AS '
    || C.FUNCT
    || '_VALUE ,';

然后在查询中设置"活动/非活动"状态