Home » SQL & PL/SQL » SQL & PL/SQL » Transposing distinct row entities into columns while counting them (PL/SQL)
Transposing distinct row entities into columns while counting them [message #427951] Mon, 26 October 2009 09:25 Go to next message
cagdaskanar
Messages: 2
Registered: October 2009
Location: Istanbul
Junior Member
Dear Friends,

In fact I guess there has been sent a lot of messages related to the topic I'll ask but I really couldn't find the exact topic that I look for.

I've prepared a sample data but I'll tell also:

I have an activities table which includes all the flight activities of our members. In the sample data you will see just 3 columns which are Member_ID , Origin, Destination.

The thing that I want is to transpose all the distinct origin-destination routes which are written in the rows to columns.

My sample data is below:

FLYER_ID ORIGIN DESTINATION
TK002467361 IST NYC
TK001713982 IST SHA
TK001459541 STR IST
TK001758418 IST AMM
TK001698377 IST AMM
TK001311445 IST PAR
TK002416860 IST NYC
TK001705770 IST NYC
TK001407060 IST NYC
TK001705770 ANK IST
TK001828800 ADA IST
TK001238286 IST CGN
TK001262136 IST IEV
TK001262136 ANK IST
TK001238286 ADA IST
TK001741037 IST BAK
TK001165342 IST SSX
TK002408635 LON IST
TK002408635 IST ECN
TK002408635 ECN IST

And I want a display like that:

FLYER_ID IST-NYC IST-PAR ANK-IST ADA-IST etc.
TK001262136 1 2 0 0
TK002408635 2 0 0 0

Thanks for your helps.

Regards,
Re: Transposing distinct row entities into columns while counting them [message #427964 is a reply to message #427951] Mon, 26 October 2009 09:51 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
In fact I guess there has been sent a lot of messages related to the topic I'll ask but I really couldn't find the exact topic that I look for.

So work with what you found and post what you tried.
Post a working Test case: create table and insert statements along with the result you want with these data.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), 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
Re: Transposing distinct row entities into columns while counting them [message #428012 is a reply to message #427951] Mon, 26 October 2009 16:34 Go to previous messageGo to next message
cagdaskanar
Messages: 2
Registered: October 2009
Location: Istanbul
Junior Member
Dear Sir,

I really couldnt find the exact solution that I need.
I am a freshman for PL/SQL, so it might be a very easy question for you, but it's really for me to develop my skills. Please
give a hand to me, I really need a support.
Re: Transposing distinct row entities into columns while counting them [message #428013 is a reply to message #428012] Mon, 26 October 2009 17:05 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
There's no easy way to do this. Try this search http://www.google.com/search?q=dynamic+pivot+oracle

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

Dynamic pivot is easiest to achieve when output is XML.
http://www.oracle.com/technology/pub/articles/oracle-database-11g-top-features/11g-pivot.html
Re: Transposing distinct row entities into columns while counting them [message #428045 is a reply to message #428012] Tue, 27 October 2009 00:27 Go to previous message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Please give a hand to me, I really need a support.

Please help us to help you and post what I asked:
Quote:
Post a working Test case: create table and insert statements along with the result you want with these data.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), 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
Previous Topic: Missing keyword to update related column
Next Topic: UTL_SMTP ULT_TCP
Goto Forum:
  


Current Time: Sun Sep 25 00:56:41 CDT 2016

Total time taken to generate the page: 0.06800 seconds