SQL SERVER/ORACLE 将ID串列转换为多行的写法

SQL SERVER 将ID串列转换为多行的写法：

select distinct a.employeeid as MANAGEREMPLOYEEID
  , CAST(SUBSTRING(a.AttributeValue,number,CHARINDEX(',',a.AttributeValue+',',number)-number) AS INT) as MEMBEREMPLOYEEID
from dbo.tbEmployeeAttribute a, master..spt_values
where number >= 1 
  and number < len(a.AttributeValue)
  and a.AttributeFlag = 15
  and SUBSTRING(','+a.AttributeValue,number,1) = ',';

原表数据：

执行结果如下：

 

ORACLE 将ID串列转换为多行的写法：

CREATE OR REPLACE VIEW ROLE.TEAMEMEMBER AS
SELECT a.employeeid MANAGEREMPLOYEEID, b.column_value MEMBEREMPLOYEEID
FROM role.employeeattribute a
     , table(CONCEPT.fnIDInString(a.attributevalue,',')) b
WHERE a.attributecode = 15;

原表数据：

SQL执行转换之后：

 

附录：CONCEPT.fnIDInString 自定义函数

CREATE OR REPLACE FUNCTION CONCEPT.fnIDInString(p_string IN VARCHAR2, p_delimiter IN VARCHAR2)
    RETURN  type_str_split  
    PIPELINED
AS
    v_length   NUMBER := LENGTH(p_string);
    v_start    NUMBER := 1;
    v_index    NUMBER;
BEGIN
    WHILE(v_start <= v_length)
    LOOP
        v_index := INSTR(p_string, p_delimiter, v_start);

        IF v_index = 0
        THEN
            PIPE ROW(SUBSTR(p_string, v_start));
            v_start := v_length + 1;
        ELSE
            PIPE ROW(SUBSTR(p_string, v_start, v_index - v_start));
            v_start := v_index + 1;
        END IF;
    END LOOP;

    RETURN;
END ;

日期、时间戳和字符串互转格式：

SQL> SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
-------------------------------------------------------
2023-02-14 11:44:27

SQL> SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SSxFF') FROM DUAL;
-------------------------------------------------------
ORA-01821: 日期格式无法识别

SQL> SELECT TO_CHAR(current_timestamp(5),'YYYY-MM-DD HH24:MI:SSxFF') FROM DUAL;
-------------------------------------------------------
2023-02-14 11:43:45.89415

SQL> SELECT TO_DATE('2023-02-14 11:04:28','yyyy-mm-dd HH:mi:ss') FROM DUAL;
-------------------------------------------------------
2023/2/14 11:04:28

SQL> SELECT TO_DATE('2023-02-14 11:39:26.34183','YYYY-MM-DD HH24:MI:SSXFF') FROM DUAL;
-------------------------------------------------------
ORA-01821: 日期格式无法识别

SQL> SELECT TO_TIMESTAMP('2023-02-14 11:39:26.34183','YYYY-MM-DD HH24:MI:SSXFF') FROM DUAL;
-------------------------------------------------------
14-2月 -23 11.39.26.341830000 上午

SQL> SELECT TO_TIMESTAMP('2023-02-14 11:39:26','YYYY-MM-DD HH24:MI:SS') FROM DUAL;
-------------------------------------------------------
14-2月 -23 11.39.26.000000000 上午