I have a view with the following columns
user_name VARCHAR2 (100 Byte)
FULL_NAME VARCHAR2 (240 Byte)
FO VARCHAR2 (30 Byte)
BUDGET_PURPOSE VARCHAR2 (150 Byte)
GRANT_ACCOUNT VARCHAR2 (240 Byte)
END_DATE DATE
UNIT_OFFICER VARCHAR2 (30 Byte)
UNIT VARCHAR2 (10 Byte)
CAMPUS VARCHAR2 (15 Byte)
ACCESS_SOURCE VARCHAR2 (13 Byte)
Sample data:
user_name FULL_NAME FO BUDGET_PURPOSE GRANT_ACCOUNT END_DATE UNIT_OFFICER UNIT CAMPUS ACCESS_SOURCE
123456789012 Smith, Adam E Smith, Adam E 206533 N Smith, Adam E 20456 Boston Budget Office
123456789012 Smith, Adam E Smith, Adam E 206533 N Smith, Adam E 20456 Boston FO
123456789012 Smith, Adam E Smith, Adam E 206566 N Smith, Adam E 20456 Boston Budget Office
123456789012 Smith, Adam E Smith, Adam E 206566 N Smith, Adam E 20456 Boston FO
987654321098 Bates, Beverly MAUE, LEA C 202198 N ANTHONY, DAVID 20096 Boston Budget Office
987654321098 Bates, Beverly MAUE, LEA C 202198 N ANTHONY, DAVID 20096 Boston FO
987654321098 Bates, Beverly MAUE, LEA C 206428 N ANTHONY, DAVID 20077 Boston Budget Office
987654321098 Bates, Beverly MAUE, LEA C 206429 N ANTHONY, DAVID 20077 Boston FO
I want a query that will give me the columns below in a tree like structure:
User_name Unit Budget_purpose
123456789012 -> 20456 -> 206533
206566
987654321098 -> 20096 -> 202198
-> 20077 -> 206428
206429
The view does not contain hierarchical data in the sense there is no parent - child realationship between user name and any other columns, but I want a tree like structure with unqiue value displaying only once per user name. Any idea how to do that?
Thank You,
AS