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?
mahjagAsked:
Who is Participating?
 
fhsyedConnect With a Mentor Commented:
mujahid .  how can u get a single row with 3 column headings ( with spaces in between )
you yourlsef have asked u need an output like this

I think the output that I need is

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



and my answer give you

ASSIGNMENT_ID ASG_TITLE             ASG_EMPCAT
------------- --------------------- --------------------
          100 ;Comp info            ;AP
          100 ;Comp info;Technician ;AP;CS
isnt that qualify ?

0
 
SharathData EngineerCommented:
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

0
 
fhsyedCommented:
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)

0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
fhsyedCommented:
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

0
 
mahjagAuthor Commented:
I think the output that I need is

ASSIGNMENT_ID ASG_TITLE             ASG_EMPCAT
------------- --------------------- --------------------
   
          100 ;Comp info;Technician ;AP;CS
0
 
SharathData EngineerCommented:
Did you execute my query? Post the result of the query and your expected result?
0
 
mahjagAuthor Commented:
I am sorry that I did not get a chance to run the query - I will do that EOD today
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.