How can I change the format RESULT OF SQL from this to this?-CodePudding

Now my reuslt format is this:

CodePudding user response：

If you need rows as CSV then we can try it this way:
1. Sample data to work with

WITH
    tbl  AS
        (
            Select 
              To_Date('12-07-2022', 'dd-mm-yyyy') "LOG_DTM", 'dhcdbs1a.server.ehr.gov.hk' "A_HOST_NAME", 'almdpopr' "USER_CD", 
              'ALM_DP_XXX' "SEARCH_VARCHAR_1", 'savejobpwd' "SEARCH_VARCHAR_2", 'almassup_xxxx' "SEARCH_VARCHAR_3", 'D' "SEARCH_VARCHAR_4",
              'createaccountjob' "OPERATION_CD", 'ACC 1' "ACC_HOST", 'SUP_XXX_YYYY' "SERVICE_CD", 'START' "ACTION_CD"
            From Dual Union All
            Select 
              To_Date('21-08-2022', 'dd-mm-yyyy') "LOG_DTM", 'dhcdbs1a.server.ehr.gov.hk' "A_HOST_NAME", 'almrinsr' "USER_CD", 
              'ALM_DP_OPR' "SEARCH_VARCHAR_1", 'savejobpwd' "SEARCH_VARCHAR_2", 'almassup_dhcweb' "SEARCH_VARCHAR_3", Null "SEARCH_VARCHAR_4",
              'getjobpwd' "OPERATION_CD", 'ACC 2' "ACC_HOST", 'SUP_PCM_CMD' "SERVICE_CD", 'END' "ACTION_CD"
            From Dual Union All
            Select 
              To_Date('24-08-2022', 'dd-mm-yyyy') "LOG_DTM", 'dhcdbs1a.server.ehr.gov.hk' "A_HOST_NAME", 'almopror' "USER_CD", 
              'ALM_DP_YYY' "SEARCH_VARCHAR_1", 'canceljobusr' "SEARCH_VARCHAR_2", 'almassup_yyyy' "SEARCH_VARCHAR_3", 'L' "SEARCH_VARCHAR_4",
              'deactiveacctjob' "OPERATION_CD", 'ACC 3' "ACC_HOST", 'SUP_PCM_CMD' "SERVICE_CD", 'END' "ACTION_CD"
            From Dual 
        ),
--  LOG_DTM   A_HOST_NAME                USER_CD  SEARCH_VARCHAR_1 SEARCH_VARCHAR_2 SEARCH_VARCHAR_3 SEARCH_VARCHAR_4 OPERATION_CD     ACC_HOST SERVICE_CD   ACTION_CD
--  --------- -------------------------- -------- ---------------- ---------------- ---------------- ---------------- ---------------- -------- ------------ ---------
--  12-JUL-22 dhcdbs1a.server.ehr.gov.hk almdpopr ALM_DP_XXX       savejobpwd       almassup_xxxx    D                createaccountjob ACC 1    SUP_XXX_YYYY START     
--  21-AUG-22 dhcdbs1a.server.ehr.gov.hk almrinsr ALM_DP_OPR       savejobpwd       almassup_dhcweb                   getjobpwd        ACC 2    SUP_PCM_CMD  END       
--  24-AUG-22 dhcdbs1a.server.ehr.gov.hk almopror ALM_DP_YYY       canceljobusr     almassup_yyyy    L                deactiveacctjob  ACC 3    SUP_PCM_CMD  END

2. Declare your SQL as dataset CTE to get the data rows you want

I removed the GROUP BY clause and put a fix value of '1' to column FREQUENT because it returns 1 for all rows anyway
You should check why you are using TO_TIMESTAMP function in WHERE clause along with TRUNC to month - it is not needed (!?)

    dataset AS
        (
            SELECT 
              TO_CHAR (LOG_DTM,'yyyy-mm-dd') "RECORD_DATE",
              A_HOST_NAME "PROCESS_HOST", 
              USER_CD "OS_USER", 
              SEARCH_VARCHAR_1 "PCM_USER",
              OPERATION_CD "OPERATION_CD",
              SEARCH_VARCHAR_2 "ACC_NAME",
              SEARCH_VARCHAR_3 "TOOL_TYPE_CD", 
              SEARCH_VARCHAR_4  "ACC_HOST",
              '1' "FREQUENT"
            FROM 
              tbl
            WHERE 
              SERVICE_CD = 'SUP_PCM_CMD' AND
              ACTION_CD = 'END' AND
              OPERATION_CD in ('getjobpwd', 'createaccountjob', 'savejobpwd', 'deactiveacctjob') AND
              LOG_DTM between TRUNC(ADD_MONTHS(TO_TIMESTAMP('01-08-2022','DD-MM-YYYY'), -1),'MM') AND
              LAST_DAY(ADD_MONTHS(TRUNC(TO_TIMESTAMP('01-09-2022','DD-MM-YYYY'),'mm'),-1))
        )
--  RECORD_DATE PROCESS_HOST               OS_USER  PCM_USER   OPERATION_CD     ACC_NAME     TOOL_TYPE_CD    ACC_HOST FREQUENT
--  ----------- -------------------------- -------- ---------- ---------------- ------------ --------------- -------- --------
--  2022-08-21  dhcdbs1a.server.ehr.gov.hk almrinsr ALM_DP_OPR getjobpwd        savejobpwd   almassup_dhcweb          1        
--  2022-08-24  dhcdbs1a.server.ehr.gov.hk almopror ALM_DP_YYY deactiveacctjob  canceljobusr almassup_yyyy   L        1

3. Create CSV header and data rows using concatination chars ||

    SELECT 
        'RECORD_DATE,PROCESS_HOST,OS_USER,PCM_USER,OPERATION_CD,ACC_NAME,TOOL_TYPE_CD,ACC_HOST,FREQUENT' "MY_CSV" 
    FROM Dual 
Union All
    SELECT 
        Nvl(RECORD_DATE, '') || ',' ||
        Nvl(PROCESS_HOST, '') || ',' ||
        Nvl(OS_USER, '') || ',' ||
        Nvl(PCM_USER, '') || ',' ||
        Nvl(OPERATION_CD, '') || ',' ||
        Nvl(ACC_NAME, '') || ',' ||
        Nvl(TOOL_TYPE_CD, '') || ',' ||
        Nvl(ACC_HOST, '')|| ',' ||
        FREQUENT "MY_CSV" 
    FROM dataset
--  
--  R e s u l t
--  
--  MY_CSV  
--  -----------------------------------------------------------------------------------------------------------
--  RECORD_DATE,PROCESS_HOST,OS_USER,PCM_USER,OPERATION_CD,ACC_NAME,TOOL_TYPE_CD,ACC_HOST,FREQUENT              
--  2022-08-21,dhcdbs1a.server.ehr.gov.hk,almrinsr,ALM_DP_OPR,getjobpwd,savejobpwd,almassup_dhcweb,,1           
--  2022-08-24,dhcdbs1a.server.ehr.gov.hk,almopror,ALM_DP_YYY,deactiveacctjob,canceljobusr,almassup_yyyy,L,1

Regards...