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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.