Home » SQL & PL/SQL » SQL & PL/SQL » queried data from column into rows
queried data from column into rows [message #428995] Sat, 31 October 2009 17:29 Go to next message
amdabd
Messages: 91
Registered: November 2007
Location: My Computer
Member
hi
how can I get my queried data from column into rows

./fa/6944/0/

thanks
  • Attachment: 0100.png
    (Size: 14.37KB, Downloaded 252 times)
Re: queried data from column into rows [message #428996 is a reply to message #428995] Sat, 31 October 2009 17:50 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Search the forum first, this gets asked about six times each week.
Re: queried data from column into rows [message #428997 is a reply to message #428995] Sat, 31 October 2009 18:25 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>Search the forum first, this gets asked about six times each week.

& the key words to SEARCH for are "rows to columns", "columns to rows", or PIVOT

In fact if you are using V11 Oracle, PIVOT is now an option of SELECT statement.


You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
Re: queried data from column into rows [message #429018 is a reply to message #428995] Sun, 01 November 2009 01:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a working Test case: create table and insert statements along with the result you want with these data.

Also always post your Oracle version with 4 decimals.

Regards
Michel
Re: queried data from column into rows [message #429256 is a reply to message #428995] Mon, 02 November 2009 17:54 Go to previous messageGo to next message
amdabd
Messages: 91
Registered: November 2007
Location: My Computer
Member
hi
sorry for my previous post.

my table consists of a name (customer) and the date of transaction made by that customer.

SQL> desc date_event
 Name                  Null?    Type
 -------------------- -------- --------------
 ENAME                           VARCHAR2(50)
 EDATE                           DATE
SQL> select * from date_event order by 2;

ENAME                          EDATE
----------------------------- ----------
jones                          01-01-2009
ford                           02-01-2009
jones                          03-01-2009
smith                          04-01-2009
ford                           04-01-2009
jones                          09-01-2009
smith                          09-01-2009
jones                          15-01-2009
smith                          15-01-2009
ford                           16-01-2009
jones                          19-01-2009
smith                          20-01-2009
jones                          23-01-2009
smith                          24-01-2009
smith                          28-01-2009
jones                          30-01-2009
smith                          01-02-2009
jones                          03-02-2009
smith                          06-02-2009
jones                          07-02-2009
smith                          12-02-2009
jones                          18-02-2009

22 rows selected.
SQL>

my target is to get date of transaction for each month "grouped by" customer
as
name   month   transaction1 trans2     trans3     trans4     trans5     trans6     trans7   trans..>
-----  ------- -----------  ---------- ---------- ---------- ---------- ---------- ------
jones  January  01-01-2009  03-01-2009 09-01-2009 15-01-2009 19-01-2009 23-01-2009 30-01-2009
jones  February 03-02-2009  07-02-2009 18-02-2009

ford   January  02-01-2009  04-01-2009 16-01-2009

smith  January  04-01-2009  09-01-2009 15-01-2009 20-01-2009 24-01-2009 28-01-2009
smith  February 01-02-2009  06-02-2009 12-02-2009

thanks -------------------------------------------------------------------------------------------------------------------------------------

[Updated on: Mon, 02 November 2009 17:59]

Report message to a moderator

Re: queried data from column into rows [message #429257 is a reply to message #429256] Mon, 02 November 2009 18:28 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
What should occur when multiple rows exist for same name & same date?
Re: queried data from column into rows [message #429259 is a reply to message #428995] Mon, 02 November 2009 19:30 Go to previous messageGo to next message
amdabd
Messages: 91
Registered: November 2007
Location: My Computer
Member
>What should occur when multiple rows exist for same name & same date?

that will not be happened,

in this example I used names instead of foreign keys related to customers table. in this table (date_event ) one record is inserted for any customer has one or more transactions within a day.

Oracle version 10.2

regards

[Updated on: Mon, 02 November 2009 19:46]

Report message to a moderator

Re: queried data from column into rows [message #429277 is a reply to message #429259] Tue, 03 November 2009 00:30 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
is there any limit on maximum number of transactions a person can do in a year??
Re: queried data from column into rows [message #429286 is a reply to message #429256] Tue, 03 November 2009 01:00 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Sun, 01 November 2009 08:09
Post a working Test case: create table and insert statements along with the result you want with these data.

Also always post your Oracle version with 4 decimals.

Regards
Michel

[Updated on: Tue, 03 November 2009 01:01]

Report message to a moderator

Previous Topic: merging select into one
Next Topic: ORA-30926: unable to get a stable set of rows in the source tables
Goto Forum:
  


Current Time: Sun Dec 04 02:55:21 CST 2016

Total time taken to generate the page: 0.08617 seconds