Home » SQL & PL/SQL » SQL & PL/SQL » Getting data from 3 rows with three fields to 1 row with 9 fields (Oracle 10.2.0.1.0)
Getting data from 3 rows with three fields to 1 row with 9 fields [message #428265] Tue, 27 October 2009 11:32 Go to next message
DanJohnson
Messages: 3
Registered: October 2009
Location: Lancaster UK
Junior Member
Hi,

I've got a problem which I can't gt my head round and can't find an answer to anywhere on the web.

I've got to produce some output to be loaded into a new application some-one has bought. My the problem is the new app can't cope with multiple rows for a single person but requires all the fields to be in a single line.

I want to turn data in a table which looks like this

User Fld1 Fld2 Fld3
1 A B C
1 D Null F
1 J E G
2 K B C
2 A P C
2 A B Z

Into this

User Fld1(1) Fld2(1) Fld3(1) Fld1(2) Fld2(2) Fld3(2) Fld1(3) Fld2(3) Fld3(3)
1 A B C D Null F J E G
2 K B C A P C A B Z
Etc.

Unfortunately I this page has lost the formatting of the above diagram Sad

I also need to be sure that fields which don't have any data in them return a null value or the wretched app will have a nervous breakdown.

I don't mind if I have to build a temp table to hold the data before exporting it, or just do the export from the main table. What I can't work out is how to 3 lines with 3 fields (not including the key field User) into a single row with 9 fields.

I'm pretty sure it can be done but can't get my head around how to do it.

Any help gratefully received, thanks in advance

Dan
Re: Getting data from 3 rows with three fields to 1 row with 9 fields [message #428266 is a reply to message #428265] Tue, 27 October 2009 11:52 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
First off this should be in the PL/SQL forum not suggestions and feedback.
Secondly you can preserve formatting if you use code tags - see the orafaq forum guide for details.
Third - have a search for 'pivot'.

EDIT: typo

[Updated on: Tue, 27 October 2009 11:52]

Report message to a moderator

Re: Getting data from 3 rows with three fields to 1 row with 9 fields [message #428267 is a reply to message #428265] Tue, 27 October 2009 11:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Unfortunately I this page has lost the formatting of the above diagram

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Use code tags and "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

This is a standard PIVOT question we have answered many times so I'm pretty surprised you didn't find any answer here or on the web.

Also post a working Test case: create table and insert statements along with the result you want with these data then you will faster and more detailed answers.

Regards
Michel
Re: Getting data from 3 rows with three fields to 1 row with 9 fields [message #428455 is a reply to message #428265] Wed, 28 October 2009 07:14 Go to previous messageGo to next message
DanJohnson
Messages: 3
Registered: October 2009
Location: Lancaster UK
Junior Member
Hi,

Sorry I posted this in the wrong place, I'm new to this site and thought I was in the PL/SQL forum.

I did include my Oracle version, 10.2.0.1.0, so I'm not quite sure why it didn't show here, maybe I should have typed 10.2.0.1?

My problem doesn't seem to be a PIVOT question, as I understand the output of PIVOT, this was probably my fault as I was trying to ask my question in as simple a manner as possible, this might have led to some confusion, so here goes with a fuller explanation.

All the references to PIVOT seem to do perform the following function on data:~

US, Jan, $100
US, Feb, $300
US, Mar, $500
Austria, Jan, $130
Austria, Feb, $80
Austria, Mar, $110
Mexico, Jan, $200
Mexico, Feb, $250
Mexico, Mar, $400

Is PIVOTED into

Jan Feb Mar
US $100 $300 $500
Austria $130 $80 $110
Mexico $200 $250 $400

Which is vey nice and in it's own way very useful but not what I need.

What I need is:

US, Jan, $100, Feb, $300, Mar, $500
Austria, Jan, $130, Feb, $80, Mar, $110
Mexico, Jan, $200, Feb, $250, Mar, $400

So my issue is:~

I have a table called SRS_CAP with 141 fields, there are between 1 and 5 entries in the table for any user.

I need to select data from a number of tables (including SRS_CAP) and export it as a CSV file for importing into a third party application. Three of the tables are easy as I get unique vales but I also need to select data from 27 fields in the CAP_SRS table and output them concatenated to the data extracted from the first three tables, as a single line per user in the CSV file. If there are less than 5 records for any user in the CAP_SRS table I must provide set of field delimiters with a null value for all the missing fields. Therefore every line of the export file must have 147 fields, the first 12 fields being the unique data from the first three tables and the next 135 being from the 27 re-iterated columns in the CAP SRS table.

I can get the data out of the first three tables without any problem and the SRS_CAP table has a key data field CAP_STUC which links to the unique data and identifies each line.

I think I need to build a temporary table with 136 fields to hold the data from the SRS_CAP table consisting of the CAP_STUC field and the five iterations of the 27 fields I need to extract. Once the data is loaded into this table from the SRS_CAP table I will be able to build the extract as the users require.

What I can't find on the web or this board (and I have looked at great length) is, after I have selected my data from the SRS_CAP table, how I can get the first row of a user's records in the SRS_CAP table inserted into the first iteration of the corresponding columns in my temporary table, the second row of a user's records in the SRS_CAP table inserted into the second iteration of the corresponding columns in my temporary table etc. up to the max of 5 rows in the SRS_CAP table. Pivot does not seem to fit the bill as far as I can see, if I'm wrong an example would be nice.

If there is an alternative to my temporary table method which will allow me to achieve my export I'd be interested in that as well.

Dan
Re: Getting data from 3 rows with three fields to 1 row with 9 fields [message #428462 is a reply to message #428455] Wed, 28 October 2009 07:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Here are a couple of solutions:
SQL> select deptno, job, ename from t order by 1,2;
    DEPTNO JOB       ENAME
---------- --------- ----------
        10 PRESIDENT KING
        20 ANALYST   FORD
        20 CLERK     ADAMS
        20 MANAGER   JONES
        30 CLERK     JAMES
        30 MANAGER   BLAKE
        30 SALESMAN  ALLEN

7 rows selected.

SQL> col names format a80 heading "JOB/EMPLOYEE_NAME LIST"
SQL> select deptno, wm_concat(job||','||ename) names
  2  from t
  3  group by deptno
  4  order by deptno
  5  /
    DEPTNO JOB/EMPLOYEE_NAME LIST
---------- --------------------------------------------
        10 PRESIDENT,KING
        20 ANALYST,FORD,MANAGER,JONES,CLERK,ADAMS
        30 CLERK,JAMES,MANAGER,BLAKE,SALESMAN,ALLEN

3 rows selected.

SQL> select deptno, 
  2         substr(replace(sys_connect_by_path(name,'/'),'/',','),2) names
  3  from ( select deptno, job||','||ename name, 
  4                row_number() over(partition by deptno order by job, ename) rn
  5         from t
  6       )
  7  where connect_by_isleaf = 1
  8  connect by prior deptno = deptno and prior rn = rn-1
  9  start with rn = 1
 10  /
    DEPTNO JOB/EMPLOYEE_NAME LIST
---------- ----------------------------------------------------------------------
        10 PRESIDENT,KING
        20 ANALYST,FORD,CLERK,ADAMS,MANAGER,JONES
        30 CLERK,JAMES,MANAGER,BLAKE,SALESMAN,ALLEN

3 rows selected.

SQL> select deptno, 
  2         trim (both ',' from 
  3         replace(
  4         replace(
  5           max(decode(job,'ANALYST',job))||','||max(decode(job,'ANALYST',ename))||','||
  6           max(decode(job,'CLERK',job))||','||max(decode(job,'CLERK',ename))||','||
  7           max(decode(job,'MANAGER',job))||','||max(decode(job,'MANAGER',ename))||','||
  8           max(decode(job,'PRESIDENT',job))||','||max(decode(job,'PRESIDENT',ename))||','||
  9           max(decode(job,'SALESMAN',job))||','||max(decode(job,'SALESMAN',ename)),
 10           ',,,',','),
 11           ',,',',')
 12         )  names
 13  from emp
 14  group by deptno
 15  order by deptno
 16  /
    DEPTNO JOB/EMPLOYEE_NAME LIST
---------- --------------------------------------------------------------------------------
        10 PRESIDENT,KING
        20 ANALYST,SCOTT,CLERK,SMITH,MANAGER,JONES
        30 CLERK,JAMES,MANAGER,BLAKE,SALESMAN,WARD

3 rows selected.

The first one use wm_concat which an undocumented function in 10g. You can use T. Kyte's STRAGG instead in 9i, 10g and 11gR1 and LISTAGG standard one in 11gR2.
The last one implies you know the list of categories, if they are months it is OK.

Regards
Michel

[Updated on: Wed, 28 October 2009 07:47]

Report message to a moderator

Re: Getting data from 3 rows with three fields to 1 row with 9 fields [message #428519 is a reply to message #428462] Wed, 28 October 2009 10:42 Go to previous messageGo to next message
DanJohnson
Messages: 3
Registered: October 2009
Location: Lancaster UK
Junior Member
Michel,

Thanks, the wm_concat is going in the right direction, I've just got to pad out the missing records and it's finished.

Dan
Re: Getting data from 3 rows with three fields to 1 row with 9 fields [message #428521 is a reply to message #428519] Wed, 28 October 2009 10:45 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Remember it is an undocumented function, better use T. Kyte's equivalent one.

Regards
Michel
Previous Topic: connect by prior question
Next Topic: Missing right parenthesis?
Goto Forum:
  


Current Time: Mon Dec 05 18:56:26 CST 2016

Total time taken to generate the page: 0.17088 seconds