Home » SQL & PL/SQL » SQL & PL/SQL » converting unknown amount of rows to columns (solaris, 10g)
converting unknown amount of rows to columns [message #425375] Thu, 08 October 2009 17:31 Go to next message
derrywriter
Messages: 8
Registered: October 2009
Junior Member
Hi all,

I have a data set containing salary history as below which I have simplified


CREATE TABLE
theset
(
empno varchar(10)
,pname varchar(20)
,sdate date
,edate date
,sal number(5))
;


INSERT INTO theset VALUES('12345','George','01-jan-2009','30-jan-2009',25000);
INSERT INTO theset VALUES('12345','George','01-feb-2009','28-feb-2009',30000);
INSERT INTO theset VALUES('98765','John','01-feb-2009','28-feb-2009',30000);
INSERT INTO theset VALUES('98765','John','01-mar-2009','31-mar-2009',35000);
INSERT INTO theset VALUES('99999','Joe','01-feb-2007','28-feb-2007',30000);
INSERT INTO theset VALUES('11111','Mary','01-jan-1988','01-jan-1998',15000);
INSERT INTO theset VALUES('11111','Mary','02-jan-1988','01-jan-2008',25000);
INSERT INTO theset VALUES('11111','Mary','01-jan-2008','30-sep-2009',15000);


This returns as

select empno, pname, sdate, edate, sal from theset

empno pname sdate edate sal
12345 George 01-JAN-09 30-JAN-09 25000
12345 George 01-FEB-09 28-FEB-09 30000
98765 John 01-FEB-09 28-FEB-09 30000
98765 John 01-MAR-09 31-MAR-09 35000
99999 Joe 01-FEB-07 28-FEB-07 30000
11111 Mary 01-JAN-88 01-JAN-98 15000
11111 Mary 02-JAN-88 01-JAN-08 25000
11111 Mary 01-JAN-08 30-SEP-09 15000

I have been asked to return the above as ONE ROW for each employee and for the life of me I can't figure it out.

So it should read as below

empno,pname, sdate,edate,sal, sdate,edate,sal, sdate,edate,sal...
empno,pname, sdate,edate,sal..and so on etc
empno,pname, sdate,edate,sal, sdate,edate,sal,...etc

The problem is that sdate,edate,sal needs to be repeated on a single row for as many rows each employee has at detail level and I do not know in advance the maximum rows that each employee has, one may have 2 rows while another 30 rows

Any ideas?

Thanks in advance

derrywriter
Re: converting unknown amount of rows to columns [message #425378 is a reply to message #425375] Thu, 08 October 2009 18:20 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
>Any ideas?
Search this forum for keyword "PIVOT".

>'01-jan-2009'
In Oracle characters between single quote marks are STRINGS.
'this is a string, 2009-10-08, not a date'
When you need a DATE datatype use TO_DATE() function

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/


Re: converting unknown amount of rows to columns [message #425379 is a reply to message #425378] Thu, 08 October 2009 18:37 Go to previous messageGo to next message
derrywriter
Messages: 8
Registered: October 2009
Junior Member
Thanks Blackswan.

I had looked at PIVOT but it seems to assume a fixed number of columns eg 12 x month columns.

My problem is that my column amount is variable.

dw
Re: converting unknown amount of rows to columns [message #425380 is a reply to message #425379] Thu, 08 October 2009 19:04 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
>I had looked at PIVOT but it seems to assume a fixed number of columns

ASSUME?

Perhaps you should reconsider.

http://www.orafaq.com/node/1871


http://www.lmgtfy.com/?q=oracle+row+to+column+example+sql

[Updated on: Thu, 08 October 2009 19:40]

Report message to a moderator

Re: converting unknown amount of rows to columns [message #425593 is a reply to message #425375] Fri, 09 October 2009 17:18 Go to previous messageGo to next message
derrywriter
Messages: 8
Registered: October 2009
Junior Member
Hi Blackswan,

Thanks for your help and links but they are confined to the same issue I am trying to overcome.

In the orafaq link you posted Kevin Meade illustrates taking a source table of 2 rows of retail transaction info and combining it to 1 row

However, he 'knows' in advance in this scenario that he will have no more than 2 types of currency category, original and base, and thus he is able to create 2 x 3 case statements to produce 1 row. What if he had an unknown quantity and range of currency categories?

So he 'knows' there are 2 types of currency category.

My situation is the same as Kevin's only I do not have a Currency Category type field to base the pivot around.

Imagine my source dataset was exactly the same as Kevin's below, 2 rows but WITHOUT the currency category, how would you envision getting these onto 1 row, bearing in mind that each terminal may have multiple transactions?

Currency
RETAIL_SALE_ID TERMINAL_ID SALE_DATE Category TTL_AMT TTL_DIS TTL_TAX
-------------- ----------- --------- -------- ------- ------- -------
1 1.00 14-JUN-07 ORIGINAL 100.00 20.00 4.80
2 1.00 14-JUN-07 BASE 117.00 23.40 5.62

thanks in advance, Oracle newbie alert as well lol

dw

[Updated on: Fri, 09 October 2009 17:19]

Report message to a moderator

Re: converting unknown amount of rows to columns [message #425607 is a reply to message #425375] Sat, 10 October 2009 01:12 Go to previous messageGo to next message
Michel Cadot
Messages: 63812
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select deptno||','||wm_concat(ename||','||sal||','||job)
  2  from emp
  3  group by deptno
  4  /
DEPTNO||','||WM_CONCAT(ENAME||','||SAL||','||JOB)
------------------------------------------------------------------------------------------------------------------------
10,CLARK,2450,MANAGER,KING,5000,PRESIDENT,MILLER,1300,CLERK
20,SMITH,800,CLERK,FORD,3000,ANALYST,ADAMS,1100,CLERK,SCOTT,3000,ANALYST,JONES,2975,MANAGER
30,ALLEN,1600,SALESMAN,BLAKE,2850,MANAGER,MARTIN,1250,SALESMAN,TURNER,1500,SALESMAN,JAMES,950,CLERK,WARD,1250,SALESMAN

3 rows selected.

Regards
Michel
Re: converting unknown amount of rows to columns [message #425827 is a reply to message #425607] Mon, 12 October 2009 07:15 Go to previous messageGo to next message
derrywriter
Messages: 8
Registered: October 2009
Junior Member
Michel Cadot wrote on Sat, 10 October 2009 07:12
SQL> select deptno||','||wm_concat(ename||','||sal||','||job)
  2  from emp
  3  group by deptno
  4  /
DEPTNO||','||WM_CONCAT(ENAME||','||SAL||','||JOB)
------------------------------------------------------------------------------------------------------------------------
10,CLARK,2450,MANAGER,KING,5000,PRESIDENT,MILLER,1300,CLERK
20,SMITH,800,CLERK,FORD,3000,ANALYST,ADAMS,1100,CLERK,SCOTT,3000,ANALYST,JONES,2975,MANAGER
30,ALLEN,1600,SALESMAN,BLAKE,2850,MANAGER,MARTIN,1250,SALESMAN,TURNER,1500,SALESMAN,JAMES,950,CLERK,WARD,1250,SALESMAN

3 rows selected.

Regards
Michel



Hi Michel,

Thanks for this but it does not work.


It seems that the system I am working on is version 9.2.0.8.0 and it does no recognise the WM_CONCAT keyword.

Any ideas?

Thanks,

dw
Re: converting unknown amount of rows to columns [message #425830 is a reply to message #425827] Mon, 12 October 2009 07:25 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
WM_CONCATE function is owned by WMSYS schema. And is available in Oracle 9.2 (Windows).

Read documentation here.

regards,
Delna
Re: converting unknown amount of rows to columns [message #425834 is a reply to message #425830] Mon, 12 October 2009 07:34 Go to previous messageGo to next message
derrywriter
Messages: 8
Registered: October 2009
Junior Member
delna.sexy wrote on Mon, 12 October 2009 13:25
WM_CONCATE function is owned by WMSYS schema. And is available in Oracle 9.2 (Windows).



regards,
Delna



Thanks for this.

More hurdles, the box I am on is running Unix, I guess the wm_concat function is only available to windows server installs?

dw

[Updated on: Mon, 12 October 2009 07:35]

Report message to a moderator

Re: converting unknown amount of rows to columns [message #425837 is a reply to message #425834] Mon, 12 October 2009 07:45 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Don't know about Oracle for Unix system. It is available in Oracle for Windows as documented.
You can google or may contact your DBA or any other experienced person for your problem.

regards,
Delna
Re: converting unknown amount of rows to columns [message #425839 is a reply to message #425837] Mon, 12 October 2009 07:50 Go to previous messageGo to next message
ThomasG
Messages: 3186
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Also, Tom Kytes Stragg does basically the same thing as wm_concat.
Re: converting unknown amount of rows to columns [message #425840 is a reply to message #425839] Mon, 12 October 2009 08:00 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
http://www.oracle-base.com/articles/10g/StringAggregationTechniques.php

sriram
Re: converting unknown amount of rows to columns [message #425843 is a reply to message #425827] Mon, 12 October 2009 08:12 Go to previous messageGo to next message
Michel Cadot
Messages: 63812
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
It seems that the system I am working on is version 9.2.0.8.0 and it does no recognise the WM_CONCAT keyword.

Your title specified "10g".
If you give wrong information, you have inappropriate answer.
I hate wasting my time.
You're lucky others give you the solution because myself I stop helping in this case.

Regards
Michel
Re: converting unknown amount of rows to columns [message #425844 is a reply to message #425843] Mon, 12 October 2009 08:15 Go to previous messageGo to next message
derrywriter
Messages: 8
Registered: October 2009
Junior Member
Michel Cadot wrote on Mon, 12 October 2009 14:12
Quote:
It seems that the system I am working on is version 9.2.0.8.0 and it does no recognise the WM_CONCAT keyword.

Your title specified "10g".
If you give wrong information, you have inappropriate answer.
I hate wasting my time.
You're lucky others give you the solution because myself I stop helping in this case.

Regards
Michel


Hi Michel,

Apologies for wasting your time, I did not mean to do that.

dw
Re: converting unknown amount of rows to columns [message #425852 is a reply to message #425844] Mon, 12 October 2009 09:17 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Use STRAGG instead of WM_CONCAT - WM_CONCAT is an undocumented oracle function, which basically means that Oracle are free to alter how it works or even remove it in later versions of Oracle.

Previous Topic: full outer join (merged)
Next Topic: Improve running time
Goto Forum:
  


Current Time: Fri Sep 30 10:45:40 CDT 2016

Total time taken to generate the page: 0.06194 seconds