我有一个sql Server实例,它具有到Oracle服务器的链接服务器. Oracle服务器上有一个名为PersonOptions的表,其中包含以下数据：
╔══════════╦══════════╗ ║ PersonID ║ OptionID ║ ╠══════════╬══════════╣ ║ 1 ║ A ║ ║ 1 ║ B ║ ║ 2 ║ C ║ ║ 3 ║ B ║ ║ 4 ║ A ║ ║ 4 ║ C ║ ╚══════════╩══════════╝
我需要转动这些数据,结果如下：
╔══════════╦═════════╦══════════╦══════════╗ ║ PersonID ║ OptionA ║ Option B ║ Option C ║ ╠══════════╬═════════╬══════════╬══════════╣ ║ 1 ║ 1 ║ 1 ║ ║ ║ 2 ║ ║ ║ 1 ║ ║ 3 ║ ║ 1 ║ ║ ║ 4 ║ 1 ║ ║ 1 ║ ╚══════════╩═════════╩══════════╩══════════╝
有什么建议？
您可以通过几种方法执行此数据转换.您可以访问PIVOT函数,这将是最简单的,但如果没有,那么您可以使用聚合函数和CASE.
聚合/案例版本：
select personid,max(case when optionid = 'A' then 1 else 0 end) OptionA,max(case when optionid = 'B' then 1 else 0 end) OptionB,max(case when optionid = 'C' then 1 else 0 end) OptionC from PersonOptions group by personid order by personid;
静态枢轴：
select *
from
(
select personid,optionid
from PersonOptions
) src
pivot
(
count(optionid)
for optionid in ('A' as OptionA,'B' OptionB,'C' OptionC)
) piv
order by personid
动态版本：
如果你有一个已知数量的值,上面的两个版本工作得很好,但如果你的值是未知的,那么你将需要实现动态sql,在Oracle中你可以使用一个过程：
CREATE OR REPLACE procedure dynamic_pivot_po(p_cursor in out sys_refcursor)
as
sql_query varchar2(1000) := 'select personid ';
begin
for x in (select distinct OptionID from PersonOptions order by 1)
loop
sql_query := sql_query ||
',min(case when OptionID = '''||x.OptionID||''' then 1 else null end) as Option_'||x.OptionID;
dbms_output.put_line(sql_query);
end loop;
sql_query := sql_query || ' from PersonOptions group by personid order by personid';
dbms_output.put_line(sql_query);
open p_cursor for sql_query;
end;
/
然后返回结果,您将使用：
variable x refcursor exec dynamic_pivot_po(:x) print x
结果与所有版本相同：
| PERSONID | OPTIONA | OPTIONB | OPTIONC | ------------------------------------------ | 1 | 1 | 1 | 0 | | 2 | 0 | 0 | 1 | | 3 | 0 | 1 | 0 | | 4 | 1 | 0 | 1 |
