21

I have a SQL Server instance that has a linked server to an Oracle server. There is a table on the Oracle server called PersonOptions which contains the following data:

╔══════════╦══════════╗
║ PersonID ║ OptionID ║
╠══════════╬══════════╣
║        1 ║ A        ║
║        1 ║ B        ║
║        2 ║ C        ║
║        3 ║ B        ║
║        4 ║ A        ║
║        4 ║ C        ║
╚══════════╩══════════╝

I need to pivot that data so the results are:

╔══════════╦═════════╦══════════╦══════════╗
║ PersonID ║ OptionA ║ Option B ║ Option C ║
╠══════════╬═════════╬══════════╬══════════╣
║        1 ║       1 ║        1 ║          ║
║        2 ║         ║          ║        1 ║
║        3 ║         ║        1 ║          ║
║        4 ║       1 ║          ║        1 ║
╚══════════╩═════════╩══════════╩══════════╝

Any suggestions?

20

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 |
  • However the Static Pivot solution assumes there are only three options. What if you have a potentially unlimited number of options? ABCDEFGHIJK for example? Isn't there a way to make the pivot dynamic with regular sql? Instead of making the options the column headers, could we just put them in the columns? So it would look like this: | PERSONID | Column2 | Column3 | Column4 | ------------------------------------------ | 1 | A | B |null| | 2 | C |null|null| | 3 | null | C | null | – Matthew Feb 5 '18 at 23:50
  • 1
    @Matthew you’d have to use Dynamic Sql as I demonstrate in the last part of the answer. – Taryn Feb 5 '18 at 23:51
  • Thanks for the quick response! I actually do this by creating a new column, and stuffing all the options in there separated by commas. The col generates from a subquery selecting from the same tables where a.personId = a2.personId order by a2.personId for xml path(''). a2 is the table in the subquery. Then I separate the data in excel using text to columns with comma as the delimiter. I was hoping to find a way to do this in regular sql without having to write a procedure, but maybe there is no way. Have to run at the moment but I'll try to post an example of that to better explain. – Matthew Feb 6 '18 at 0:05
9

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;
5

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
  • 1
    Feel free to explain this one just a little more. What does the pivot provide that the others may not? And when does that break down? Remember you're answering for posterity, not for someone with specific domain expertise in the very things you know as well. – jcolebrand Dec 24 '12 at 18:00
  • 2
    @jcolebrand: It's more about personal preference - I myself think that PIVOT syntax is more convoluted compared to approach I use. However, I'm aware both of them give the same result, and I agree other people may think opposite. – a1ex07 Dec 24 '12 at 18:31
  • 1
    Hint: Use the edit button ;-) ~ We like to encourage more than a code-answer response. – jcolebrand Dec 24 '12 at 22:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.