This discussion is archived
7 Replies Latest reply: May 26, 2010 2:55 AM by Frank Kulash RSS

Display rows into columns

589855 Newbie
Currently Being Moderated
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 Explorer
    Currently Being Moderated
    Please post the input and output data and this will helpful to produce the result asap.
  • 2. Re: Display rows into columns
    589855 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    Hi,

    The following thread discusses several options:

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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points