asked on

Merge two records to one line

Hello Experts,

The example here is a dummy data. I am trying to merge a Student records into one. Please provide your guidance how to achieve this.

Current result:

StudentID Name 'Submit Date' 'Approved by' 'Approved by ID'

1234, SAM, '10/01/2018 4:04:18 PM', Hary, hry243

1234, SAM, '10/02/2018 12:30:51 PM', John, jhn334

Expected result:

StudentID Name 'Submit Date 1' 'Approved by 1' 'Approved by ID 1' 'Submit Date 2' 'Approved by 2' 'Approved by ID2'

1234 SAM '10/01/2018 4:04:18 PM', Hary, hry243, '10/02/2018 12:30:51 PM', John, jhn334

slightwv (䄆 Netminder)

Try a PIVOT:

select
	*
 from (
	select 
	StudentID, Name, Submit_Date, Approved_by, Approved_by_ID,
		row_number() over(partition by studentid order by submit_Date) rn
from tab1
)
pivot(
	max(Submit_Date) sub_date, max(Approved_by) app_by, max(Approved_by_ID) app_by_id
	for rn in (1,2)
)
/

Open in new window

Geert G

what if you have 20 approvals ?

slightwv (䄆 Netminder)

The point Geert is trying to make is you have to know the maximum number ahead of time. Just add numbers to the PIVOT or the maximum number you will have.

AJ S

ASKER

Thanks slightwv. We are using 10g so cant use PIVOT. Sorry, should have mentioned this earlier.

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

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

Start Free Trial

AJ S

ASKER

Thanks a lot slightwv and Geert for your help.

Geert G

i wouldn't pivot the data at all
it might work for some people for some time
they soon get fed up of scrolling to the right to see the rest, and the mouse doesn't have a wheel for scrolling in that direction yet

in the end, you'll be going back to the normal layout