14 Replies Latest reply on Sep 6, 2018 12:14 PM by Solomon Yakobson

    Create all distinct value of row as separate column

    Costa

      Hello Expert,

      I have two column with ID and side effect of drug.

      I want to design the sql such that all unique side effect in whole ID will be placed as separate column.

      Then, the program need to check individual term for every ID. If it is matching, then show it 1 else 0.

      How to progress. Please help me.

       

      Note that all the column data are dynamic in nature

      create table abc as

      select 101 as ID, 'asthma' as side_Effect from dual union

      select 101, 'Heart Attack' from dual union

      select 101, 'Heart stroke' from dual union

      select 102, 'Heart Attack' from dual union

      select 102, 'asthma' from dual union

      select 103, 'asthma' from dual union

      select 104, 'asthma' from dual union

      select 104, 'Heart stroke' from dual union

      select 104, 'Heart Attack' from dual union

      select 104, 'sepsis' from dual union

      select 105, 'haematoma' from dual union

      select 106, 'asthma' from dual union

      select 106, 'haematoma' from dual;

       

      My Output

            

      IDHeart AttacksepsisasthmaHeart strokehaematoma
      10411110
      10110110
      10210100
      10300100
      10500001
      10600101
        • 1. Re: Create all distinct value of row as separate column
          L. Fernigrini

          You can start reading this section of the forum FAQ

           

          Re: 4. How do I convert rows to columns?

           

          There are different was to achieve what you want, they are all explained there

          • 3. Re: Create all distinct value of row as separate column
            Solomon Yakobson

            If you know all side effects upfront:

             

            select  *

              from  abc

              pivot(

                    count(*)

                    for side_effect in ('asthma','Heart Attack','Heart stroke','sepsis','haematoma')

                   )

            /

             

                    ID   'asthma' 'Heart Attack' 'Heart stroke'   'sepsis' 'haematoma'

            ---------- ---------- -------------- -------------- ---------- -----------

                   102          1              1              0          0           0

                   101          1              1              1          0           0

                   104          1              1              1          1           0

                   105          0              0              0          0           1

                   103          1              0              0          0           0

                   106          1              0              0          0           1

             

            6 rows selected.

             

            SQL>

             

            SY.

            1 person found this helpful
            • 4. Re: Create all distinct value of row as separate column
              Etbin

              If you want it dynamic (first step)

               

              with

              abc as

              (select 101 ID,'asthma' side_Effect from dual union all

              select 101,'Heart Attack' from dual union all

              select 101,'Heart stroke' from dual union all

              select 102,'Heart Attack' from dual union all

              select 102,'asthma' from dual union all

              select 103,'asthma' from dual union all

              select 104,'asthma' from dual union all

              select 104,'Heart stroke' from dual union all

              select 104,'Heart Attack' from dual union all

              select 104,'sepsis' from dual union all

              select 105,'haematoma' from dual union all

              select 106,'asthma' from dual union all

              select 106,'haematoma' from dual

              )

              select 'select * from abc pivot(count(side_effect) for side_effect in ('||listagg(''''||side_effect||'''',',') within group (order by side_effect)||'))' qry

                from (select distinct side_effect

                        from abc

                     )

               

              QRY
              select * from abc pivot(count(side_effect) for side_effect in ('Heart Attack','Heart stroke','asthma','haematoma','sepsis'))

               

              with

              abc as

              (select 101 ID,'asthma' side_Effect from dual union all

              select 101,'Heart Attack' from dual union all

              select 101,'Heart stroke' from dual union all

              select 102,'Heart Attack' from dual union all

              select 102,'asthma' from dual union all

              select 103,'asthma' from dual union all

              select 104,'asthma' from dual union all

              select 104,'Heart stroke' from dual union all

              select 104,'Heart Attack' from dual union all

              select 104,'sepsis' from dual union all

              select 105,'haematoma' from dual union all

              select 106,'asthma' from dual union all

              select 106,'haematoma' from dual

              )

              select * from abc pivot(count(side_effect) for side_effect in ('Heart Attack','Heart stroke','asthma','haematoma','sepsis'))

               

              ID'Heart Attack''Heart stroke''asthma''haematoma''sepsis'
              10210100
              10111100
              10411101
              10500010
              10300100
              10600110

               

              Regards

               

              Etbin

              1 person found this helpful
              • 5. Re: Create all distinct value of row as separate column
                mathguy

                Is table ABC a simplified version of an actual table you have in your schema, or is it the result of some preliminary processing?

                 

                If you have an actual table similar to ABC, then you may want to consider improving your data model first. It would be much better if you had a separate, smaller table SIDE_EFFECTS with columns ID (primary key) and SIDE_EFFECT (unique key), and your table ABC should have ID (DRUG_ID?) and SIDE_EFFECT_ID, each of them a foreign key pointing to the primary key (ID) column of the DRUGS table and the SIDE_EFFECTS table, respectively.

                 

                As it is right now, nothing prevents typos in the side effect column (resulting in it being counted as a different side effect, appearing as a separate column in the PIVOT output). Even without typos, and unnecessary spaces, etc., you may have different capitalization (why do you have Heart Attack with both words capitalized, Heart stroke with only the first word capitalized, and asthma not capitalized?) - Heart Attack and Heart attack will become different columns in your output, if you are not very careful.

                 

                Here is how I would set this up. There should be one more table, DRUGS, which I don't show since it is not needed for THIS problem; still, it is a best practice to have it.

                 

                 

                TABLES:  CREATE TABLE and INSERT

                 

                create table side_effects (

                  id          number        primary key

                , side_effect varchar2(200) unique not null

                )

                ;

                 

                insert into side_effects (id, side_effect)

                  select 1, 'Asthma'       from dual union all

                  select 2, 'Heart AttacK' from dual union all

                  select 3, 'Heart Stroke' from dual union all

                  select 4, 'Haematoma'    from dual union all

                  select 5, 'Sepsis'       from dual

                ;

                 

                create table abc (

                  drug_id        number not null     -- references drugs

                , side_effect_id number not null references side_effects

                , unique(drug_id, side_effect_id)

                )

                ;

                 

                insert into abc (drug_id, side_effect_id)

                  select 101, 1 from dual union all

                  select 101, 2 from dual union all

                  select 101, 3 from dual union all

                  select 102, 2 from dual union all

                  select 102, 1 from dual union all

                  select 103, 1 from dual union all

                  select 104, 1 from dual union all

                  select 104, 3 from dual union all

                  select 104, 2 from dual union all

                  select 104, 5 from dual union all

                  select 105, 4 from dual union all

                  select 106, 1 from dual union all

                  select 106, 4 from dual

                ;

                 

                 

                STATIC QUERY to do the pivoting:

                 

                select *

                from   abc

                pivot  (count(*) for side_effect_id in (

                                     1 as "Asthma", 2 as "Heart Attack", 3 as "Heart Stroke", 4 as "Haematoma", 5 as "Sepsis"

                                 )

                       )

                order by drug_id;

                 

                 

                   DRUG_ID     Asthma Heart Attack Heart Stroke  Haematoma     Sepsis

                ---------- ---------- ------------ ------------ ---------- ----------

                       101          1            1            1          0          0

                       102          1            1            0          0          0

                       103          1            0            0          0          0

                       104          1            1            1          0          1

                       105          0            0            0          1          0

                       106          1            0            0          1          0

                 

                 

                 

                 

                QUERY to create the PIVOT query dynamically:  (Run this query to create a text string, which is the PIVOT query you must run as a second step... copy and paste)

                 

                Note: If you have a lot of side effects to account for, the SQL string built below may become too long... then you will need to use different methods to create the IN_LIST, since LISTAGG has a limit of 4000 bytes. Not worrying about that for the time being.

                 

                with

                  prep(in_list) as (

                  select listagg(to_char(id) || ' as "' || side_effect || '"', ', ')

                            within group (order by id)

                  from   side_effects

                )

                -- select in_list from prep; */

                select 'select *'   || chr(10) ||

                       'from   abc' || chr(10) ||

                       'pivot(count(*) for side_effect_id in (' || chr(10) ||

                       '                   ' || in_list         || chr(10) ||

                       '               )'                       || chr(10) ||

                       '     )'     || chr(10) ||

                       'order by drug_id;'

                       as sql_str

                from   prep;

                 

                 

                SQL_STR

                ------------------------------------------------------------------------------------------------------------

                pivot(count(*) for side_effect_id in (

                                   1 as "Asthma", 2 as "Heart AttacK", 3 as "Heart Stroke", 4 as "Haematoma", 5 as "Sepsis"

                               )

                     )

                order by drug_id;

                • 6. Re: Create all distinct value of row as separate column
                  Costa

                  Thank you very much for providing the solution

                  • 7. Re: Create all distinct value of row as separate column
                    Costa

                    Thanks you Solomon and Mathguy for the solution and guidance

                    • 8. Re: Create all distinct value of row as separate column
                      Costa

                      While applying the logic,  I am getting below error as the number of side effect is >2000 and number of records >25000.

                      How to resolve it

                      ORA-01489: result of string concatenation is too long

                      • 9. Re: Create all distinct value of row as separate column
                        mathguy

                        Better question:

                         

                        Are you really looking to create a table, a view, or a report with more than 2000 columns? WHY? Who is ever going to read it? (Hint: the answer is nobody). Or, rather, what other process will need to have the results lined up in columns, as you are trying to do? It is very likely that whatever the ultimate need, arranging the data in more than 2000 columns is not the right way to go. (And, to be clear - the maximum number of columns in an Oracle table or view is 1000, regardless of the method used to create it. NO CAN DO!)

                        • 10. Re: Create all distinct value of row as separate column
                          Costa

                          Thanks Mathguy for explaining in details.

                          My requirement is to create one table with this data. I am using some front end tool to import this data for predictive analytics.

                          If possible, can we keep all records in one column so that I will use excel macro to split to multiple column.

                           

                          Thanks once again.

                          • 11. Re: Create all distinct value of row as separate column
                            AndrewSayer

                            Costa wrote:

                             

                            Thanks Mathguy for explaining in details.

                            My requirement is to create one table with this data. I am using some front end tool to import this data for predictive analytics.

                            If possible, can we keep all records in one column so that I will use excel macro to split to multiple column.

                             

                            Thanks once again.

                            No, your requirement would be something like ‘display the information in a friendly format’. Specifying that every different side effect needs to be viewed as a new column somewhere is an attempt at a technical solution.

                             

                            Most decent analytic front end tools can do their own pivoting with very minimal effort. I suggest you try it that way.

                            • 12. Re: Create all distinct value of row as separate column
                              Solomon Yakobson

                              Costa wrote:

                               

                              While applying the logic, I am getting below error as the number of side effect is >2000 and number of records >25000.

                              How to resolve it

                              ORA-01489: result of string concatenation is too long

                               

                              Use XMLAGG instead of LISTAGG:

                               

                              with t as (select distinct side_effect from abc)

                              select  'select * from abc pivot(count(side_effect) for side_effect in (' ||

                                      rtrim(xmlcast(xmlagg(xmlelement(e,'''' || side_effect || ''',')) as clob),',') || '))' qry

                                from  t

                              /

                               

                              QRY

                              ------------------------------------------------------------------------------------------------------------------------------------

                              select * from abc pivot(count(side_effect) for side_effect in ('Heart stroke','asthma','haematoma','sepsis','Heart Attack'))

                               

                              SQL> select * from abc pivot(count(side_effect) for side_effect in ('Heart stroke','asthma','haematoma','sepsis','Heart Attack'));

                               

                                      ID 'Heart stroke'   'asthma' 'haematoma'   'sepsis' 'Heart Attack'

                              ---------- -------------- ---------- ----------- ---------- --------------

                                     102              0          1           0          0              1

                                     101              1          1           0          0              1

                                     104              1          1           0          1              1

                                     105              0          0           1          0              0

                                     103              0          1           0          0              0

                                     106              0          1           1          0              0

                               

                              6 rows selected.

                               

                              SQL>

                               

                              SY.

                              1 person found this helpful
                              • 13. Re: Create all distinct value of row as separate column
                                mathguy

                                Somehow I anticipated such attempts in Reply 9...   Do you think using xmlagg, or any other method, will allow us to get an output with more than 1000 columns? If the documentation is correct (sometimes it isn't), that won't be possible regardless of the method used to build the query; actually it will be impossible regardless of what query we try to write, not just the PIVOT query.

                                • 14. Re: Create all distinct value of row as separate column
                                  Solomon Yakobson

                                  Oops, you're right. Issue is # of columns, not LISTAGG.

                                   

                                  SY.