OraFAQ Forum: SQL & PL/SQL » COLUMN TO ROW & ROW TO COLUMN

Home » SQL & PL/SQL » SQL & PL/SQL » COLUMN TO ROW & ROW TO COLUMN (Oracle 10G ,Window XP)

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

COLUMN TO ROW & ROW TO COLUMN [message #563925]

Sat, 18 August 2012 03:44

visuora@yahoo.com
Messages: 13
Registered: September 2008

Junior Member

Hi All,

Please find attached document. It has data extracted from query.

In that i need to convert some columns data to row and all rows to column.

Columns to row:

SALES
OTHER_OUTFLOWS
DISBURSEMENTS
MVA_OUTFLOWS

Row To Column

FACT_TRANSACTION
FACT_TRANSACTION_DETAIL
FACT_FLOW_AGGREGATE

Attachment: DATA.jpg

SELECT jobname, last_run_date, channel, sub_channel, pcode, SALES,
other_outflows, disbursements, mva_outflows
FROM TBL_BI_JOBS WHERE last_run_date='20-JAN-2005'

Please help me on this.

Thanks
Viswanathan.S

Attachment: DATA.jpg
(Size: 112.16KB, Downloaded 27 times)

Report message to a moderator

Re: COLUMN TO ROW & ROW TO COLUMN [message #563927 is a reply to message #563925]

Sat, 18 August 2012 04:33

Michel Cadot
Messages: 54672
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

With any SQL question, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel

Report message to a moderator

Re: COLUMN TO ROW & ROW TO COLUMN [message #563928 is a reply to message #563925]

Sat, 18 August 2012 05:21

visuora@yahoo.com
Messages: 13
Registered: September 2008

Junior Member

Hi All,

Please find attached excel.

It has original data and data should be changed sample.

Thanks
Viswanathan.S

Attachment: data_should be.csv
(Size: 31.50KB, Downloaded 32 times)

Report message to a moderator

Re: COLUMN TO ROW & ROW TO COLUMN [message #563929 is a reply to message #563928]

Sat, 18 August 2012 05:45

Michel Cadot
Messages: 54672
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

I cannot execute an excel file in SQL*Plus.
Is there anything you don't understand in the following sentence; if so then tell us I will clear it:

Quote:

Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Regards
Michel

[Updated on: Sat, 18 August 2012 05:46]

Report message to a moderator

Re: COLUMN TO ROW & ROW TO COLUMN [message #563930 is a reply to message #563925]

Sat, 18 August 2012 06:19

visuora@yahoo.com
Messages: 13
Registered: September 2008

Junior Member

Hi ,

Table TBL_BI_JOBS got loaded from different 3 scripts(Cursor statments).

We have to change the row to column and column to row in table TBL_BI_JOBS.

Normal result query:

SELECT jobname, last_run_date, channel, sub_channel, pcode, SALES,
other_outflows, disbursements, mva_outflows
FROM TBL_BI_JOBS WHERE last_run_date='20-JAN-2005'

we have to modify this query for data should be as like in excel sheet.

Thanks
Viswanathan.S

Report message to a moderator

Re: COLUMN TO ROW & ROW TO COLUMN [message #563931 is a reply to message #563930]

Sat, 18 August 2012 07:37

John Watson
Messages: 3174
Registered: January 2010
Location: Global Village

Senior Member

OK, I'll have try.
You need to provide the statements that will create the table and insert the rows (not all te rows, just a representative sample). Only then can I (or anyone else) design a SELECT statement against that table that will return the data in the form you require. It is not possible to select something from nothing.

Report message to a moderator

Re: COLUMN TO ROW & ROW TO COLUMN [message #563948 is a reply to message #563931]

Sat, 18 August 2012 13:24

matthewmorris68
Messages: 133
Registered: May 2012
Location: Orlando, FL

Senior Member

Try this:

CREATE TABLE twisted
  (
    "JOBNAME"        VARCHAR2(60 BYTE),
    "LAST_RUN_DATE"  DATE,
    "CHANNEL"        VARCHAR2(60 BYTE),
    "SUB_CHANNEL"    VARCHAR2(60 BYTE),
    "PCODE"          VARCHAR2(60 BYTE),
    "SALES"          NUMBER,
    "OTHER_OUTFLOWS" NUMBER,
    "DISBURSEMENTS"  NUMBER,
    "MVA_OUTFLOWS"   NUMBER
  );

SELECT 'SALES', last_run_date, channel, sub_channel, pcode, 
       sales AS FACT_TRANSACTION,
       sales AS FACT_TRANSACTION_DETAIL,
       sales AS FACT_FLOW_AGGREGATE
FROM   twisted
UNION
SELECT 'OTHER_OUTFLOWS', last_run_date, channel, sub_channel, pcode, 
       OTHER_OUTFLOWS AS FACT_TRANSACTION,
       OTHER_OUTFLOWS AS FACT_TRANSACTION_DETAIL,
       OTHER_OUTFLOWS AS FACT_FLOW_AGGREGATE
FROM   twisted
UNION
SELECT 'DISBURSEMENTS', last_run_date, channel, sub_channel, pcode, 
       DISBURSEMENTS AS FACT_TRANSACTION,
       DISBURSEMENTS AS FACT_TRANSACTION_DETAIL,
       DISBURSEMENTS AS FACT_FLOW_AGGREGATE
FROM   twisted
UNION
SELECT 'MVA_OUTFLOWS', last_run_date, channel, sub_channel, pcode, 
       MVA_OUTFLOWS AS FACT_TRANSACTION,
       MVA_OUTFLOWS AS FACT_TRANSACTION_DETAIL,
       MVA_OUTFLOWS AS FACT_FLOW_AGGREGATE
FROM   twisted;

Report message to a moderator

Re: COLUMN TO ROW & ROW TO COLUMN [message #563950 is a reply to message #563948]

Sat, 18 August 2012 13:59

Michel Cadot
Messages: 54672
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Bad and expensive query.

Regards
Michel

Report message to a moderator

Re: COLUMN TO ROW & ROW TO COLUMN [message #563954 is a reply to message #563950]

Sat, 18 August 2012 14:43

matthewmorris68
Messages: 133
Registered: May 2012
Location: Orlando, FL

Senior Member

>Bad and expensive query

Michel:

I fully understand why a large number of your posts give people requesting assistance grief for not doing a better job of supplying enough information when they ask their questions or for acting like it is the duty of the experts on this board to answer their questions for free.

I must say that I don't understand, or see any value whatsoever in you making that particular post in response to my attempt to help this individual. I consider your reply to display incredibly bad manners. I don't know why you felt the need to post it and frankly I don't care.

If you have a better solution in mind, by all means post it. If you want to precede your SQL with something like 'This should be faster and less expensive', then that is valuable input (for the poster and possibly for me as well).

Report message to a moderator

Re: COLUMN TO ROW & ROW TO COLUMN [message #563955 is a reply to message #563954]

Sat, 18 August 2012 15:04

Michel Cadot
Messages: 54672
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Even if you don't care I'd say these answers are made for people to think and ask, if they don't ask I assume they don't care.

Now you ask so the first point is that you use UNION when you should use UNION ALL, this will prevent Oracle from doing sort and distinct operations that are obviously not required here as subqueries have an empty intersection.

The second point is that "rows to columns" and "columns to rows" questions are asked every week and efficient solutions are well-known; in the case of what you posted ("columns to rows") they are all based on a row generator.
As OP does not care to post us a test case, I don't care to post him a complete solution.

Regards
Michel

Report message to a moderator

Re: COLUMN TO ROW & ROW TO COLUMN [message #563959 is a reply to message #563955]

Sat, 18 August 2012 15:34

matthewmorris68
Messages: 133
Registered: May 2012
Location: Orlando, FL

Senior Member

I'll grant you the UNION vs UNION ALL point.

The rest of your reply is about the poster, not me. I don't care what you care about the OP. If you don't care enough to post a solution, then you might consider extending that attitude to the point where you don't care enough to post snarky comments about solutions that are posted. I don't buy the logic that it's intended to make people think -- other that to make people think uncomplimentary things about the person who posted it. In that, you succeeded. I thought.

I don't particularly care to argue about this (or anything). However, had your post simply suggested the use of UNION ALL rather than UNION, then there wouldn't have been anything to argue about.

Report message to a moderator

Re: COLUMN TO ROW & ROW TO COLUMN [message #563965 is a reply to message #563959]

Sun, 19 August 2012 00:57

Michel Cadot
Messages: 54672
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Query with generator equivalent to matthewmorris68's one:

with lines as (select level line from dual connect by level <= 4)
select decode(line, 1,'SALES', 2,'OTHER_OUTFLOWS', 3,'DISBURSEMENTS', 4,'MVA_OUTFLOWS') "TYPE",
       last_run_date, channel, sub_channel, pcode, 
       decode(line, 1,sales , 2,OTHER_OUTFLOWS, 3,DISBURSEMENTS, 4,MVA_OUTFLOWS) FACT_TRANSACTION,
       decode(line, 1,sales , 2,OTHER_OUTFLOWS, 3,DISBURSEMENTS, 4,MVA_OUTFLOWS) FACT_TRANSACTION_DETAIL,
       decode(line, 1,sales , 2,OTHER_OUTFLOWS, 3,DISBURSEMENTS, 4,MVA_OUTFLOWS) FACT_FLOW_AGGREGATE
from twisted, lines
/

Regards
Michel

Report message to a moderator

Previous Topic:	ORA-00947: not enough values
Next Topic:	With Clause

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Jun 18 19:42:18 CDT 2013

Total time taken to generate the page: 0.11077 seconds