Home » SQL & PL/SQL » SQL & PL/SQL » SQL Select - Data from one row output to separate rows
SQL Select - Data from one row output to separate rows [message #197108] Tue, 10 October 2006 01:56 Go to next message
attraxion
Messages: 14
Registered: October 2006
Junior Member
Hi,

It's so very tempting to post a question here and wait for replies to come.. Smile

How can I use a single select statement to display data from one row (based on some conditions) in different rows? It's like this, I have a table:

DEPTT CITY MGR1 MGR2 MGR3 MGR4
Finance DEL Ram Raghu Kavita John
Sales DEL Amit Manu

and I want to display data like this:

DEPTT CITY MGR_NAME MGR_NO
Finance DEL Ram 1
Finance DEL Raghu 2
Finance DEL Kavita 3
Finance DEL John 4
Sales DEL Amit 1
Sales DEL Manu 2

All I can think of right now is 4 select statements each with WHERE MGR1 IS NOT NULL etc type of clause. Obviously a smarter way must exist.

Thanks and regards,
Attraxion.
Re: SQL Select - Data from one row output to separate rows [message #197291 is a reply to message #197108] Tue, 10 October 2006 21:40 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You could join to a ROW GENERATOR (search the board), or you could cast the MGR columns to a VARRAY or Nested Table and then use a TABLE() function to normalise them.

Something like

SELECT DEPTT, CITY, column_value AS MGR
FROM (
  SELECT DEPTT, CITY, SYS.DBMS_DEBUG_VC2COLL(MGR1, MGR2, MGR3, MGR4) AS mgr
  FROM mytab
) a
, table(b.mgr)


Ross Leishman

[Updated on: Tue, 10 October 2006 21:40]

Report message to a moderator

Previous Topic: Abt Trigger
Next Topic: Why DBMS_STATS.GATHER_TABLE_STATS is not working
Goto Forum:
  


Current Time: Sat Dec 10 20:15:12 CST 2016

Total time taken to generate the page: 0.08107 seconds