Compare row values - oracle 9 sql

Please see the attached displaying - 1. the current table data and - 2. the desired output.

This data is looking at clients, assessments undertaken on them, and answers given in these assessments.

If a clients set of answers is replicated on another clients answers, I'd like the set of answers to only appear once and the clients numbers to be listed.

in this example, the question is asking about the clients ethnicity - there are 4 separate answers to the question - START_DATE, END_DATE, CAT_DESC and NOTES. As you can see in the answers given for clients 147 and 8456 in the AVD_DATA (answers column) matches so the clients answers only appear once.

I'm not sure if this is possible. Any help is appreciated.
Answers-Report-1-.xls
tonMachine100Asked:
Who is Participating?
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.

jfmadorCommented:
Hello,

You will need to use aggregation function on your clientId, to do it you will need to convert your client number as a varchar and create an aggregation function under oracle because there is no built-in function to do string aggregation

You can take a look to this web page there is few examples about how to implement it

http://www.oracle-base.com/articles/10g/StringAggregationTechniques.php
0
richard_cristCommented:
The EE link below also discusses your question.  It has several comments and a reference to another EE link.  Between jfmador's link and the EE one you may be able to find something that works in your situation.  If you do please accept jfmador's comment, as that is what led me to the EE link (because his comment led to my searching some more).

jfmador, thanks for you comment.  I will find this discussion very useful in the future.  Also, thank you tonMachine100 for bringing this up.   :)
0
jfmadorCommented:
Hey Richard you forget to leave your EE link

is it this one ?
http://www.experts-exchange.com/Database/Oracle/9.x/Q_23835552.html
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

richard_cristCommented:
Sorry!   :P

It is http://www.experts-exchange.com/Databases/Oracle/Q_21429673.html

Thanks for the catch!   :)

0
SujithData ArchitectCommented:
Try this.

create or replace function test_func(p_QST_TYPE tbl1.QST_TYPE%type,
                                     p_QST_CODE tbl1.QST_CODE%type,
				     p_AVD_DATA tbl1.AVD_DATA%type,
				     p_QST_DESC tbl1.QST_DESC%type,
				     p_ANV_NAME tbl1.ANV_NAME%type,
				     p_ANV_ID   tbl1.ANV_ID%type)
return varchar2
as
 l_ret_str varchar2(200);
begin
 for rec in(select distinct to_char(client_id) cl_id from <your table>
            where QST_TYPE = p_QST_TYPE
	    and   QST_CODE = p_QST_CODE
	    and   AVD_DATA = nvl(p_AVD_DATA, AVD_DATA)
	    and   QST_DESC = p_QST_DESC 
	    and   ANV_NAME = p_ANV_NAME
	    and     ANV_ID = p_ANV_ID ) loop
  l_ret_str := l_ret_str ||','|| rec.cl_id;
 end loop;
 return ltrim(l_ret_str, ',');
end;
/
 
select test_func(QST_TYPE, QST_CODE, AVD_DATA, QST_DESC, ANV_NAME, ANV_ID) ids, QST_TYPE, QST_CODE, AVD_DATA, QST_DESC, ANV_NAME, ANV_ID
from <your table>
group by QST_TYPE, QST_CODE, AVD_DATA, QST_DESC, ANV_NAME, ANV_ID
order by 1;

Open in new window

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
tonMachine100Author Commented:
Thankyou both for your help.
Do you think the same output could be derived from just using sql (ie no pl)? I'm no expert with pl, so i'd find a solution in just sql easier to understand!
thanks again.
0
SujithData ArchitectCommented:
It can be done with sql. Not a direct one, but with some tricky stuff in it. Using hierarchical queries and stuff.
But the sql will get too huge as there are many join columns, which makes it much difficult to maintain the code. This is neater.
0
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.