标签：多行 串列 AttributeValue number SERVER SQL 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 ;
标签：多行,
串列,
AttributeValue,
number,
SERVER,
SQL,
ID
From： https://www.cnblogs.com/chriskwok/p/16706716.html