Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2009
    Posts
    262

    Unanswered: SQL query result - Avoid repetition of columns - readability in report

    I am getting responses from users when I give them a query to not repeat the column values
    and also build comma separated column value if it is more than one for readablity.
    suppose I have a report that has columns and data value as
    account name order Product Type code value
    zys limited 1-1A Computer Elec 1
    zys limited 1-1A Computer Elec-Print 1

    in the above report Product computer has got 2 type values and so we have 2 rows to represent the value but user wants to see the report as
    account name order product type code value
    zys limited 1-1A Computer Elec,Elec-Print 1

    not repeat the column value and also comma separate the more than 1 value

    I have oracle 11 g and may be some analytical functions can do this without much custom code

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    If you have Oracle 11gR2, use LISTAGG function as aggregate.
    Otherwise, you would have to write some code, as described in this thread on AskTom: http://asktom.oracle.com/pls/asktom/...:2196162600402 (except the first method, also investigate usage of sys_connect_by_path, stay away from wm_concat, as it is undocumented)

  3. #3
    Join Date
    Aug 2009
    Posts
    262
    create table foo
    (accountname varchar2(50),
    order varchar2(20),
    Product varchar2(20),
    Type varcahr2(20),
    codevalue number)

    insert into foo values( 'zys limited' ,'1-1A' ,'Computer' ','Elec',' 1' )
    insert into foo values( 'zys limited' ,'1-1A' ,'Computer' ','Elec-Print ',' 1' )

    can you please show me your work around ..
    in this form using the listagg

    account name order product type code value
    zys limited 1-1A Computer Elec,Elec-Print 1

  4. #4
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by mishaalsy View Post
    can you please show me your work around ..
    in this form using the listagg
    Workaround? I would call it standard tool. Just a remark - displaying one row instead of multiple ones (what you want) is called aggregation and differs from analytics, when all source rows are displayed with some values computed from other rows.

    I do not have access to 11gR2; anyway, according to the documentation (I sent the link in previous post) it should be something like
    Code:
    select accountname, order, product, listagg(type, ',') within group (order by type), codevalue
    from foo
    group by accountname, order, product, codevalue
    In the future, please try to avoid typos (varcahr2, extra non-matching quotes); I was also unable to create table as ORDER is a keyword.

  5. #5
    Join Date
    Aug 2009
    Posts
    262
    thank you fly boy .

    i am in a flight to another city using laptop which unfortunately do not have database installed .

    one of the developers asked me this and i did not have any where else to go .

    this is why i asked a workaround or querry form of reply so i can forward it to him .

    you dont have 11gr2 and i dont even have D of database ....
    i think the feeling is mutual working blindly .

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    tell the developer to look into writing a function that would concatenate all the values comma-delimited. easy to do. just call the function and pass in the PK and have the function loop through all your types and concat them.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •