Pivot rows into multiple columns
There are a few ways that you can perform this data transformation. You have access to the PIVOT
function then that will be the easiest, but if not then you can use an aggregate function and a CASE
.
Aggregate /Case version:
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;
See SQL Fiddle with Demo
Static Pivot:
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
See SQL Fiddle with Demo
Dynamic Version:
The two versions above work great if you have a known number of values, but if your values are unknown, then you will want to implement dynamic sql and in Oracle you can use a procedure:
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;
/
Then you return the results, you will use:
variable x refcursor
exec dynamic_pivot_po(:x)
print x
The results are the same with all versions:
| PERSONID | OPTIONA | OPTIONB | OPTIONC |
------------------------------------------
| 1 | 1 | 1 | 0 |
| 2 | 0 | 0 | 1 |
| 3 | 0 | 1 | 0 |
| 4 | 1 | 0 | 1 |
This would be the equivalent in SQL Server syntax. Based on my reading of the Oracle docs, NULLIF and PIVOT appear to have the same format as their SQL Server kin. The challenge will be the pivot list which needs to be static unless you make the query dynamic as Itzik demonstrates but I have no idea if that can be translated to P/SQL
WITH PersonOptions(PersonID, OptionId) AS
(
SELECT 1, 'A'
UNION ALL SELECT 1, 'B'
UNION ALL SELECT 2, 'C'
UNION ALL SELECT 3, 'B'
UNION ALL SELECT 4, 'A'
UNION ALL SELECT 4, 'C'
)
SELECT
P.PersonId
, NULLIF(P.A, 0) AS OptionA
, NULLIF(P.B, 0) AS OptionB
, NULLIF(P.C, 0) AS OptionC
FROM
PersonOptions PO
PIVOT
(
COUNT(PO.OptionId)
FOR OPtionId IN (A, B, C)
) P;
I prefer to pivot query manually, but you may use PIVOT
as well.
SELECT PersonID,
MAX(CASE WHEN OptionId ='A' THEN 1 END) AS OptionA,
MAX(CASE WHEN OptionId ='B' THEN 1 END) AS OptionB,
MAX(CASE WHEN OptionId ='C' THEN 1 END) AS OptionC
FROM PersonOptions
GROUP BY PersonID