7 Replies Latest reply: May 26, 2010 4:55 AM by Frank Kulash RSS

    Display rows into columns

    589855
      Hi,

      I have report which is having 3 set of rows data displayed instead of displaying in rows. It should show in column wise.

      please suggest me in modifying the query.


      SELECT
      wwv_flow_item.text(4,CHECK_POINTS,30,100) "CEMLI Name",
      wwv_flow_item.SELECT_LIST(5,'Yes','Yes;Yes,No;No,NA;NA') "Confimation",
      wwv_flow_item.text(6,null,30,3000) "Comments"
      from CHECK_POINTS
      where
      pro_id = :P65_PROJECT_ID and TAS_ID = :P66_TASK_ID


      Thanks
      Sudhir
        • 1. Re: Display rows into columns
          boopathi
          Please post the input and output data and this will helpful to produce the result asap.
          • 2. Re: Display rows into columns
            589855
            hi,


            There is no input here only the output has to be provided in column fashion.

            Eg: select name from employees


            Thanks will result to

            name
            -------
            ABC
            XYZ
            TTD

            I required out put wht i need is the rows should in column wise

            i.e ABC XYZ TTD


            Thanks
            Sudhir
            • 3. Re: Display rows into columns
              Frank Kulash
              Hi, Sudhir,
              Sudhir_N wrote:
              hi,


              There is no input here only the output has to be provided in column fashion.
              Don't be ridic; of course there's input. The data in yur check_points table is the input to this query. Post CREATE TABLE and INSERT statements so that people can re-create a version of your table, with just a few rows. Include only the columns that play some role in this problem.
              Also post the output you want from that sample data, if it is not exactly what you have below.
              Eg: select name from employees


              Thanks will result to

              name
              -------
              ABC
              XYZ
              TTD

              I required out put wht i need is the rows should in column wise

              i.e ABC XYZ TTD
              It sounds like you need String Aggregation .
              The following page shows several ways to do it:
              http://www.oracle-base.com/articles/10g/StringAggregationTechniques.php

              If you want three separate columns ('ABC' in one, 'XYZ' in another, 'TTD' in a third), then that's called a Pivot . It's more complicated than string aggregation, so you might want to avoid it.
              • 4. Re: Display rows into columns
                589855
                Hi,

                I have a table like this Check_Points as table name inside table contents are stored as follows

                ID CHECK_POINT
                1 AAA
                2 BBB
                3 CCC


                I need to write a report where the values should become columns


                result must be AAA BBB CCC

                Please suggest

                Thanks
                Sudhir

                Edited by: Sudhir_N on May 26, 2010 12:06 AM
                • 5. Re: Display rows into columns
                  680268
                  check following example.

                  create table temp1 (name varchar2(10),location varchar2(20));
                  insert into temp1 values ('XYZ','LOC1');
                  insert into temp1 values ('XYZ','LOC2');
                  insert into temp1 values ('XYZ','LOC3');
                  insert into temp1 values ('XYZ','LOC4');

                  commit;
                  select name, max(decode(rownum,1,location,null)), max(decode(rownum,2,location,null)),
                  max(decode(rownum,3,location,null)),max(decode(rownum,4,location,null)) from temp1 group by name;
                  • 6. Re: Display rows into columns
                    589855
                    Thanks this helped me very much..


                    If the table is having 4 rows i would be able to retrieve the data but table is having more then 4 rows say N-rows how modify the query. Please suggest me..



                    Thanks
                    Sudhir.
                    • 7. Re: Display rows into columns
                      Frank Kulash
                      Hi,

                      The following thread discusses several options:

                      Re: Report count and sum from many rows into many columns