manipulating several lines of related data into a single line.

In oracle Developer how do you take several lines of data that are related and move them into a single line?  See attachment for example.
Example-Data-layout.xlsx
Codetxt.txt
Culwatrnca11Data AnalystAsked:
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.

SharathData EngineerCommented:
How many years of data you have for a PAT_NAME? 2016-19 and a default one?
slightwv (䄆 Netminder) Commented:
You need to know the maximum number of repeating rows that is possible.  Then you need to PIVOT.

This example allows for 8 repeating sets of values and selects 4 of them (a-d).  Just tweak it to your maximum numbers:
select
	pat_mrn_id, pat_name, lob_name,
	a_bucket_name, a_roll_status, a_roll_prd, a_benefit_amount, a_min_limit,
	a_max_limit, a_max_remaining, a_status, a_pct_reached,
	--
	b_bucket_name, b_roll_status, b_roll_prd, b_benefit_amount, b_min_limit,
	b_max_limit, b_max_remaining, b_status, b_pct_reached,
	--
	c_bucket_name, c_roll_status, c_roll_prd, c_benefit_amount, c_min_limit,
	c_max_limit, c_max_remaining, c_status, c_pct_reached,
	--
	d_bucket_name, d_roll_status, d_roll_prd, d_benefit_amount, d_min_limit,
	d_max_limit, d_max_remaining, d_status, d_pct_reached
from (
select pat_mrn_id, pat_name, lob_name, bucket_name, roll_status, roll_prd, benefit_amount, min_limit, max_limit,
	max_remaining, status, pct_reached, row_number() over(partition by PAT_MRN_ID order by PAT_MRN_ID, ROLL_PRD) rn
from tab1 t1
)
	pivot(
		max(bucket_name) bucket_name,
		max(roll_status) roll_status,
		max(roll_prd) roll_prd,
		max(benefit_amount) benefit_amount,
		max(min_limit) min_limit,
		max(max_limit) max_limit,
		max(max_remaining) max_remaining,
		max(status) status,
		max(pct_reached) pct_reached
		for rn in (1 as a,2 as b,3 as c,4 as d,5 as e,6 as f,7 as g,8 as h)
	)
/

Open in new window

Culwatrnca11Data AnalystAuthor Commented:
Sharath,

Yes 2016 - 2019 and the 99999 represents the lifetime max.
Price Your IT Services for Profit

Managed service contracts are great - when they're making you money. Yes, you’re getting paid monthly, but is it actually profitable? Learn to calculate your hourly overhead burden so you can master your IT services pricing strategy.

slightwv (䄆 Netminder) Commented:
Did you try what I posted?
Culwatrnca11Data AnalystAuthor Commented:
slightwv,

no, have not had a chance to try yet.
Culwatrnca11Data AnalystAuthor Commented:
slightwv,

From your suggestion I was able to get to my own solution.

Select
BEN2016.BUCKET_NAME, BEN2016.ROLL_STATUS, BEN2016.ROLL_PRD,BEN2016.BENEFIT_AMOUNT, BEN2016.MIN_LIMIT, BEN2016.MAX_Limit,BEN2016.MAX_REMAINING, BEN2016.STATUS,BEN2016.PERCENT_REACHED
,BEN2017.BUCKET_NAME, BEN2017.ROLL_STATUS, BEN2017.ROLL_PRD,BEN2017.BENEFIT_AMOUNT, BEN2017.MIN_LIMIT, BEN2017.MAX_Limit,BEN2017.MAX_REMAINING, BEN2017.STATUS,BEN2017.PERCENT_REACHED
,BEN2018.BUCKET_NAME, BEN2018.ROLL_STATUS, BEN2018.ROLL_PRD,BEN2018.BENEFIT_AMOUNT, BEN2018.MIN_LIMIT, BEN2018.MAX_Limit,BEN2018.MAX_REMAINING, BEN2018.STATUS,BEN2018.PERCENT_REACHED
,BEN2019.BUCKET_NAME, BEN2019.ROLL_STATUS, BEN2019.ROLL_PRD,BEN2019.BENEFIT_AMOUNT, BEN2019.MIN_LIMIT, BEN2019.MAX_Limit,BEN2019.MAX_REMAINING, BEN2019.STATUS,BEN2019.PERCENT_REACHED
,BEN9999.BUCKET_NAME, BEN9999.ROLL_STATUS, BEN9999.ROLL_PRD,BEN9999.BENEFIT_AMOUNT, BEN9999.MIN_LIMIT, BEN9999.MAX_Limit,BEN9999.MAX_REMAINING, BEN9999.STATUS,BEN9999.PERCENT_REACHED

and the WHERE Clause played out like this, repeating the process for the other years and the LIFEMAX.

LEFT JOIN (SELECT DISTINCT BEN.COVERAGE_ID, BEN.PAT_ID, CMB.BUCKET_NAME
                           ,CASE  WHEN CMB.ROLL_PERIOD_C  = 1  THEN 'Calendar Year'
                                WHEN CMB.ROLL_PERIOD_C  = 2  THEN 'Number of Days'                            
                                WHEN CMB.ROLL_PERIOD_C  = 3  THEN 'No Roll'
                                WHEN CMB.ROLL_PERIOD_C = 4   THEN 'Contract Year'
                                ELSE      ' '  END  AS ROLL_STATUS        
                                                               
                                ,BEN.ROLL_PRD_START as ROLL_PRD
                                ,BEN.BEN_AMOUNT as BENEFIT_AMOUNT
                                ,ben.min_limit
                                ,ben.max_limit  
                                ,BEN.BEN_AMOUNT -  ben.max_limit as MAX_REMAINING
                                                               
                                ,CASE  WHEN zc.limit_flag_c  = 0  THEN 'Not Exceeded'
                                WHEN zc.limit_flag_c  = 1  THEN 'Min Exceeded Only'                    
                                WHEN zc.limit_flag_c  = 2  THEN 'Max Exceeded'
                                ELSE      ' '  END  AS STATUS    
                              ,Round(Ben.Ben_Amount * 100.0 / Ben.Max_Limit, 1) as PERCENT_REACHED
                 
                            FROM HCCLSC.BEN_ACCUMULATION BEN
                            INNER JOIN HCCLSC.CLARITY_CMB CMB ON BEN.BUCKET_ID=CMB.BUCKET_ID
                            LEFT JOIN HCCLSC.PATIENT PAT ON BEN.PAT_ID=PAT.PAT_ID
                            LEFT JOIN hcclsc.zc_limit_flag zc on ben.limit_flag_c = zc.limit_flag_c
                           
                            WHERE BEN.ROLL_PRD_START = '2016' and cmb.bucket_name like '%KP(S)%' AND BEN.BEN_AMOUNT>0
) BEN2016 ON BEN2016.COVERAGE_ID=BEN.COVERAGE_ID and BEN2016.PAT_ID= BEN.PAT_ID

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
slightwv (䄆 Netminder) Commented:
It might work but is horribly inefficient.

Repeating the years that way hits the table multiple times.  Then you have the overhead of all the left joins.

PIVOT is pretty efficient.  It looks more complicated than it is.  Did you try it?

I remember setting up a test case and running what I posted.  It should have worked.
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
Exadata

From novice to tech pro — start learning today.