asked on

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.. can anyone help?

Sharath S

try this

SELECT   AccountName,order,product,code_value,
           XMLELEMENT (
              "tables",
              XMLAGG (XMLELEMENT ("table", r.type || ','))
           ).EXTRACT ('//table/text()').getStringVal ()
              AS type
    FROM   your_table r
GROUP BY   AccountName,order,product,code_value

Open in new window

fhsyed

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)

fhsyed

also look at this

create table test_asg(
  Assignment_id number(15),
  Title varchar2(50),
  Emp_cat varchar2(2)
  )    ;
insert into test_asg values(100,'Comp info','AP')    ;
insert into test_asg values(100,'Technician','CS')    ;


ORAEDT11GR2test> select assignment_id, title, emp_cat,
        row_number() over (partition by assignment_id order by assignment_id) rnum
    from test_asg
   where assignment_id = 100;


ASSIGNMENT_ID TITLE                EMP_CAT                    RNUM
------------- -------------------- -------------------- ----------
          100 Comp info            AP                            1
          100 Technician           CS                            2

ORAEDT11GR2test> select assignment_id,
          sys_connect_by_path(title,';') asg_title,
          sys_connect_by_path(emp_cat,';') asg_empcat
     from (
   select assignment_id, title, emp_cat,
          row_number() over (partition by assignment_id order by assignment_id) rnum
     from test_asg
    where assignment_id = 100
          )
    start with rnum = 1
   connect by prior rnum = rnum-1 and prior assignment_id = assignment_id
   

ASSIGNMENT_ID ASG_TITLE             ASG_EMPCAT
------------- --------------------- --------------------
          100 ;Comp info            ;AP
          100 ;Comp info;Technician ;AP;CS

Open in new window

mahjag

ASKER

I think the output that I need is

ASSIGNMENT_ID ASG_TITLE ASG_EMPCAT
------------- --------------------- --------------------

100 ;Comp info;Technician ;AP;CS

Sharath S

Did you execute my query? Post the result of the query and your expected result?

mahjag

ASKER

I am sorry that I did not get a chance to run the query - I will do that EOD today

ASKER CERTIFIED SOLUTION

fhsyed

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial