4 Replies Latest reply: Aug 2, 2012 4:24 AM by Dila RSS

    Select Statement -> How do i convert rows to columns

    Dila
      Hi, i need your help please.

      i have three options: A, B, C in table T_OPTIONS (not more and not less, it is always 3)

      no i can assign articles to this option.
      Article 1, 2, 3, 4

      in the table it looks like this

      ARTICLE_ID T_OPTIONS

      1                   A
      1                   B
      2                   C
      3                    A
      3                    C

      But now i want to have a select statement which convert rows to columns, it has to look like this

      ARTICLE A B C
      1             x x
      2             x
      3             x   x


      Can you help me!?

      Edited by: Dila on 02.08.2012 01:52

      Edited by: Dila on 02.08.2012 01:53
        • 1. Re: Select Statement -> How do i convert rows to columns
          BluShadow
          How do you convert rows to columns... well... you look in the SQL and PL/SQL forum FAQ and go to the one labelled...

          {message:id=9360005}
          • 2. Re: Select Statement -> How do i convert rows to columns
            Vivek L
            Dila wrote:
            Hi, i need your help please.

            i have three options: A, B, C in table T_OPTIONS (not more and not less, it is always 3)

            no i can assign articles to this option.
            Article 1, 2, 3, 4

            in the table it looks like this

            ARTICLE_ID T_OPTIONS

            1                   A
            1                   B
            2                   C
            3                    A
            3                    C

            But now i want to have a select statement which convert rows to columns, it has to look like this

            ARTICLE A B C
            1             x x
            2             x
            3             x   x


            Can you help me!?

            Edited by: Dila on 02.08.2012 01:52

            Edited by: Dila on 02.08.2012 01:53
            Read {message:id=9360002} and {message:id=9360005}
            SQL> ed
            Wrote file afiedt.buf
            
              1  with sample_data as
              2  (
              3  select 1 ARTICLE_ID,'A' T_OPTIONS from dual union all
              4  select 1, 'B' from dual union all
              5  select 2, 'C' from dual union all
              6  select 3, 'A' from dual union all
              7  select 3, 'C' from dual
              8  )
              9   select article_id,
             10         decode(sum(case
             11                      when t_options = 'A' then 1
             12                      else 0
             13                    end), 0, null,
             14                          'X') A,
             15         decode(sum(case
             16                      when t_options = 'B' then 1
             17                      else 0
             18                    end), 0, null,
             19                          'X') B,
             20         decode(sum(case
             21                      when t_options = 'C' then 1
             22                      else 0
             23                    end), 0, null,
             24                          'X') C
             25  from   sample_data
             26* group  by article_id
            SQL> /
            
            ARTICLE_ID A B C
            ---------- - - -
                     1 X X
                     2     X
                     3 X   X
            • 3. Re: Select Statement -> How do i convert rows to columns
              Hoek
              SQL> with t as (
                2  select 1 article_id, 'A' options from dual union
                3  select 1,'B' from dual union
                4  select 2,'C' from dual union
                5  select 3,'A' from dual union
                6  select 3,'C' from dual 
                7  )
                8  --
                9  --
               10  --
               11  select article_id article
               12  ,      max(case when options = 'A' then 'x' end) A
               13  ,      max(case when options = 'B' then 'x' end) B
               14  ,      max(case when options = 'C' then 'x' end) C
               15  from   t
               16  group by article_id
               17  order by article_id;
              
                 ARTICLE A B C
              ---------- - - -
                       1 x x
                       2     x
                       3 x   x
              
              3 rows selected.
              • 4. Re: Select Statement -> How do i convert rows to columns
                Dila
                THANKS A LOT!!!