PIVOT results from 2 tables 1

[elsenorjose]

I have searched for a way to pivot the results of a query so I can take multiple rows and instead display as a single row with multiple columns but most of the examples I have come across are based on a single table query. I have a query that joins 3 tables and I need to pivot the results.

SELECT
TBL1.LOG_ID
, TBL1.LINE
, TBL2.SURG_NAME
, TBL3.NAME TITLE
FROM TBL1, TBL2, TBL3
WHERE TBL1.SURG_ID = TBL2.PROV_ID(+)
AND TBL1.TITLE_ID = TBL3.TITLE_ID(+)

The results are multiple rows per LOG_ID because there can be multiple surgeons for one surgery (the LOG_ID). What I am trying to do is create a view that will give me results on one row per LOG_ID with the SURG_NAME, and TITLE as multiple columns based on the LINE. So, basically,

LogID Surg1 Title1 Surg2 Title2 Surg3 Title3

There may be more than 3 records retrieved but I have been asked to only retrieve the first 3 LINE numbers, literally 1-3.

Any thoughts using Oracle 10g syntax?

Thank you.

 

[SantaMufasa]

José,

I know that we can resolve your need, but to help us help you, could you please post an abbreviated set of CREATE TABLE... and INSERT INTO... commands to populate the tables so that our suggestions can more accurately meet your needs?

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[elsenorjose]

Santa,

I can get the CREATE TABLE commands but I don't know how to get the INSERT INTO commands. Would that still be helpful?

 

[SantaMufasa]

We actually don't need the CREATE TABLE statements for the entire tables, just column definitions and a few rows of data for:
[ul][li]TBL1: LOG_ID, SURG_ID, TITLE_ID, (LINE?)[/li][li]TBL2: PROV_ID, SURG_NAME[/li][li]TBL3: TITLE_ID, NAME[/li][/ul]

Even if you manually created the few rows of data, that would be fine.

BTW, what does "TBL1.LINE" represent in your query, above?


Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[elsenorjose]

Thanks Santa. Sorry for the badly formatted data, I don't know how to create a nicer looking table.

So for TBL1, the fields are LOG_ID which identify the surgery, SURG_ID which is the employee number of the surgeon, and LINE is the line number associated to the surgeon for that surgery, as there can be more than one surgeon:

Log_ID Surg_ID Line Title_Id
123 abc 1 1
123 def 2 2
123 ghi 3 3
456 jkl 1 1

TBL2 is where the Surgeon's actual name is stored. Prov_ID is FK to Surg_ID in TBL1:

Prov_ID Surg_Name
abc Smith, Adam
def Jones, Michael
ghi Bennett, David

And TBL3 holds the surgeon's titles. Title_ID is the PK joined to the FK of TBL1.

Title_Id Name
1 Primary
2 Assistant
3 Fellow
4 Intern


Thank you.

 

[SantaMufasa]

José,

When I say, "...please post an abbreviated set of CREATE TABLE... and INSERT INTO... commands to populate the tables...", here is what I meant:

create table tbl1 (log_id number,surg_id varchar2(5),ln number,title_id number);
insert into TBL1 values ('123','abc','1','1');
insert into TBL1 values ('123','def','2','2');
insert into TBL1 values ('123','ghi','3','3');
insert into TBL1 values ('456','jkl','1','1');

create table tbl2 (prov_id varchar2(5), surg_name varchar2(20));
insert into TBL2 values ('abc','Smith, Adam');
insert into TBL2 values ('def','Jones, Michael');
insert into TBL2 values ('ghi','Bennett, David');

create table tbl3 (title_id number,Name varchar2(10));
insert into tbl3 values (1,'Primary');
insert into tbl3 values (2,'Assistant');
insert into tbl3 values (3,'Fellow');
insert into tbl3 values (4,'Intern');

commit;

Now, for a possible resolution to your needs, you can use the above data plus the following SQL*Plus query (which is your original code, plus a column definition, Oracle's "WM_CONCAT" function and the "group by" clause):

column x heading "Surgical Team" format a80
SELECT TBL1.LOG_ID
      ,wm_concat('('||TBL2.SURG_NAME||'--'||TBL3.NAME||')') x
  FROM TBL1, TBL2, TBL3
 WHERE TBL1.SURG_ID = TBL2.PROV_ID(+)
   AND TBL1.TITLE_ID = TBL3.TITLE_ID(+)
 group by tbl1.log_id
/

    LOG_ID Surgical Team
---------- ---------------------------------------------------------------------------
       123 (Smith, Adam--Primary),(Jones, Michael--Assistant),(Bennett, David--Fellow)
       456 (--Primary)

2 rows selected.

Let us know if this meets your need or if you have questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[elsenorjose]

Thanks Santa. Unfortunately the end users of the data don't want the names and titles concatenated. They're going to be getting an Excel workbook that they want to filter based on names and titles. My supervisor came up with a rather inelegant solution but it works and that was to use a different view for each surgeon and their title then use a Crystal Report to pull the fields in individually. Basically:

SELECT
TBL1.LOG_ID
, TBL1.LINE
, TBL2.SURG_NAME
, TBL3.NAME TITLE
FROM TBL1, TBL2, TBL3
WHERE TBL1.SURG_ID = TBL2.PROV_ID(+)
AND TBL1.TITLE_ID = TBL3.TITLE_ID(+)
AND LINE = 1

ETC...

 

[SantaMufasa]

Yes, you can solve your need by the creation of multiple views, but if your ultimate target is an Excel spreadsheet, then I believe that an even less complex solution is to simply embed <Tab> characters (for Excel to use) between the expressions that are eventual targets for separate Excel columns:

SELECT TBL1.LOG_ID||chr(94)
      ||rtrim(replace(wm_concat(TBL2.SURG_NAME||chr(94)||TBL3.NAME||')'),'),',chr(94)),')')
  FROM TBL1, TBL2, TBL3
 WHERE TBL1.SURG_ID = TBL2.PROV_ID(+)
   AND TBL1.TITLE_ID = TBL3.TITLE_ID(+)
 group by tbl1.log_id
/

123^Smith, Adam^Primary^Jones, Michael^Assistant^Bennett, David^Fellow
456^^Primary

In the code, above, I embedded a "^" -- CHR(94) to represent each <TAB> -- CHR(9), so that you can actually see the column delimiter. You can actually use the output, above, to import into Excel if you designate to Excel a "^" as your column delimiter. If you would rather us a <TAB> character, then just substitute a CHR(9) for all of the CHR(94) expressions in my code.

Additionally, if you want an explanation of what my code does, above, please post your question here.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[elsenorjose]

I just wanted to thank you once again for your suggestions. Although my supervisor decided to go with the multiple-view approach, it's good to have these ideas for future reference.

Thank you!