| Transforming Rows to Columns from a table. [message #372442] |
Tue, 13 February 2001 17:43  |
Venkat
Messages: 110
Registered: February 2001
|
Senior Member |
|
|
I got a table/resultset with the following structure.
document_number number(10)
contact_type varchar2(10)
tel_number number(20)
Contact_type has finite number of values (say 10).
ex.. Billing, Admin.. etc
Now, the requirement is to select from that table/resultset one row for each document_number with contact_type_tel_number ( admin_tel_no, billing_tel_no etc.. )
so, the result, for a particular doc_no 1234 , should look like
doc_no admin_telnbr billing_telnbr etc_telnbr....
1234 xxx1 xxx2 xxxn..
can anybody help me out with this transformation.. please..
I am working on an Oracle 7.3 database..
Thanking you for the help in advance...
|
|
|
|
| Re: Transforming Rows to Columns from a table. [message #372445 is a reply to message #372442] |
Tue, 13 February 2001 23:29  |
DWu
Messages: 3
Registered: February 2001
|
Junior Member |
|
|
Hi Venkat,
I just read an example query that seems to work for your situation. Just join the table to itself for a number of times that is equal to 1 less than the number of distinct values in contact_type column. Suppose your table is named YR_TBL and there are 10 distinct values in the column CONTACT_TYPE, try this:
CREATE TABLE AS
SELECT A.DOCUMENT_NUMBER DOC_NO, A.TEL_NUMBER ADMIN_TELNBR, B.TEL_NUMBER BILLING_TELNBR, C.TEL_NUMBER WHATEVER_TELNBR,...J.TEL_NUMBER ETC_TELNBR
FROM YR_TBL A, YR_TBL B, YR_TBL C, ... YR_TBL J
WHERE A.CONTACT_TYPE = "ADMIN"
AND B.CONTACT_TYPE = "BILLING"
AND C.CONTACT_TYPE = "WHATEVER"
...
AND J.CONTACT_TYPE = "ETC"
Let me know whether it works or not. Will you?
|
|
|
|
| Re: Transforming Rows to Columns from a table. [message #372446 is a reply to message #372442] |
Tue, 13 February 2001 23:31 |
DWu
Messages: 3
Registered: February 2001
|
Junior Member |
|
|
Hi Venkat,
I just read an example query that seems to work for your situation. Just join the table to itself for a number of times that is equal to 1 less than the number of distinct values in contact_type column. Suppose your table is named YR_TBL and there are 10 distinct values in the column CONTACT_TYPE, try this:
CREATE TABLE YR_NEW_TBL AS
SELECT A.DOCUMENT_NUMBER DOC_NO, A.TEL_NUMBER ADMIN_TELNBR, B.TEL_NUMBER BILLING_TELNBR, C.TEL_NUMBER WHATEVER_TELNBR,...J.TEL_NUMBER ETC_TELNBR
FROM YR_TBL A, YR_TBL B, YR_TBL C, ... YR_TBL J
WHERE A.CONTACT_TYPE = "ADMIN"
AND B.CONTACT_TYPE = "BILLING"
AND C.CONTACT_TYPE = "WHATEVER"
...
AND J.CONTACT_TYPE = "ETC"
Let me know whether it works or not. Will you?
|
|
|
|
| Re: Transforming Rows to Columns from a table. [message #372465 is a reply to message #372442] |
Wed, 14 February 2001 07:22 |
me
Messages: 66
Registered: August 2000
|
Member |
|
|
select doc_no,
max( decode(contact_type, 'Admin', tel_number, 0) ) admin_telnbr,
max( decode(contact_type, 'Billing', tel_number, 0) ) billing_telnbr,
max( decode(contact_type, 'Etc..', tel_number, 0) ) etc_telnbr,
....
from table_name
where ...
group by doc_no
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
