Oracle 將資料列合併成一筆(xml_agg 同)

 方法一 SYS_CONNECT_BY_PATH

WITH QA AS

     (SELECT 'Row1' DROW, 'user1' EMP, 100 NUM  FROM DUAL

      UNION ALL

      SELECT 'Row2' DROW, 'user1' EMP, 90 NUM   FROM DUAL

       UNION ALL

      SELECT 'Row3' DROW, 'user1' EMP, 90 NUM   FROM DUAL

      UNION ALL

      SELECT 'Row4' DROW, 'user1' EMP, 80 NUM   FROM DUAL),

      QB AS (SELECT  EMP,NUM, COUNT(*) OVER (PARTITION BY  EMP  ) CNT,  ROW_NUMBER() OVER (PARTITION BY EMP  ORDER BY NUM)  SEQ    FROM QA )

           SELECT  EMP, SUBSTR(SYS_CONNECT_BY_PATH( NUM, ','), 2) COMBINE FROM QB

WHERE SEQ = CNT START WITH SEQ = 1 CONNECT BY PRIOR SEQ + 1 = SEQ AND PRIOR  EMP=EMP;     

方法二 Listagg

WITH QA AS 

     (SELECT 'Row1' DROW, 'user1' EMP, 100 NUM  FROM DUAL 

      UNION ALL 

      SELECT 'Row2' DROW, 'user1' EMP, 90 NUM   FROM DUAL 

       UNION ALL 

      SELECT 'Row3' DROW, 'user1' EMP, 90 NUM   FROM DUAL 

      UNION ALL 

      SELECT 'Row4' DROW, 'user1' EMP, 80 NUM   FROM DUAL) 

 SELECT  EMP,  LISTAGG(NUM, ',') WITHIN GROUP (ORDER BY NUM) AS  COMBINE FROM QA  group by EMP

Oracle group by 取沒有在 group by 的特定欄位列資料

with  test as (
           select '1' code, 'Get code 1'  name, 1 mpd, 600 amt,'AA' pd  from dual
union all  select '2' code, 'drop code 2'  name, 0 mpd, -600 amt,'AA' pd  from dual
union all  select '3' code, 'Gte code 3'  name, 1 mpd, 100 amt,'BB' pd  from dual 
     )
select   pd,sum(amt) as total,
         min(code) keep (dense_rank last order by mpd) as code,
         min(name) keep (dense_rank last order by mpd) as name
from     test group by pd

Oracle TABLE 移動 tablespace

變更連線
ALTER SESSION SET CURRENT_SCHEMA =Owner;
移動資料表，資料移動期間整個 table  lock 
ALTER TABLE   OBJECT_NAME   MOVE TABLESPACE  NEW;
重建Index
ALTER INDEX  INDEX_NAME REBUILD TABLESPACE NEWNDX ;

T-SQL Select 設定null 欄位型態

SELECT   CAST(NULL as VARCHAR2(100)) as  varcchar2,CAST(NULL as date) as  datetime FROM dual;