Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

How to write Transpose in Oracle Please Help Expand / Collapse
Author
Message
Posted Wednesday, August 29, 2012 2:20 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 6:48 AM
Points: 11, Visits: 16
sample date
-----------
source
------
ID-----NAME-----------ADD1--------ADD2---------ADD3------ADD4-------ADD5---------ADD6

1 ------ ABC 12B 13B 14B 15B 16B 17B
2 ------ BBC 10V VCF BB
3------ ADD 30

TARGET
-----
id ------ NAME------ADD
1-------ABC--------12B
1-------ABC--------10V
1-------ABC--------13B
1-------ABC--------14B
1-------ABC--------15B
1-------ABC--------16B
1-------ABC--------17B
2-------BBC--------10V
2-------BBC--------VCF
2-------BBC--------BB
3-------ADD--------30
Post #1351907
Posted Wednesday, August 29, 2012 2:27 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:26 PM
Points: 13,093, Visits: 12,573
You are not too likely to find a lot of Oracle people around here since this is sql server forum. Even if you do, they will ask you for the same thing. ddl (create table), sample data (insert statements) and desired output based on your sample data. From what you posted i am not exactly sure what you have as tables and what you want as output. And of course in Oracle I am useless.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1351910
Posted Wednesday, August 29, 2012 2:59 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:24 PM
Points: 12,905, Visits: 32,180
if you google "Oracle UNPIVOT", you should get some examples of this "transpose" thing you are looking for.

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1351919
Posted Thursday, August 30, 2012 6:58 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 10:08 AM
Points: 4,467, Visits: 10,809
WITH source(ID, NAME, ADD1, ADD2, ADD3, ADD4, ADD5, ADD6) AS (
SELECT 1, 'ABC', '12B', '13B', '14B', '15B', '16B', '17B' FROM dual
UNION ALL
SELECT 2, 'BBC', '10V', 'VCF', 'BB', NULL, NULL, NULL FROM dual
UNION ALL
SELECT 3, 'ADD', '30', NULL, NULL, NULL, NULL, NULL FROM dual
)
SELECT ID, NAME, "ADD"
FROM source
UNPIVOT ( "ADD" FOR col IN (ADD1, ADD2, ADD3, ADD4, ADD5, ADD6));

Tested on oracle 11.2.0.1.


--
Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Post #1352172
Posted Thursday, August 30, 2012 7:05 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:18 PM
Points: 5,189, Visits: 12,053
Gianluca Sartori (8/30/2012)

--snip
Tested on oracle 11.2.0.1.


Turncoat!



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1352178
Posted Thursday, August 30, 2012 7:16 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 10:08 AM
Points: 4,467, Visits: 10,809

Sins of a past life boiling to the surface...


--
Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Post #1352192
Posted Thursday, August 30, 2012 8:57 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 6:48 AM
Points: 11, Visits: 16
Thanks this example works perfectly...But This is an example i have provided but in real life we have table and columns names.

Can you give me this as columns names with out selecting data.
Post #1352292
Posted Thursday, August 30, 2012 9:01 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 10:08 AM
Points: 4,467, Visits: 10,809
vp7986 (8/30/2012)
Thanks this example works perfectly...But This is an example i have provided but in real life we have table and columns names.

Can you give me this as columns names with out selecting data.


Sorry, but I can't believe you can't do it yourself.


--
Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Post #1352296
Posted Thursday, August 30, 2012 9:02 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 10:08 AM
Points: 4,467, Visits: 10,809
Also, you didn't provide any table name. Is "source" the name of your table?

--
Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Post #1352298
Posted Thursday, August 30, 2012 9:05 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 6:48 AM
Points: 11, Visits: 16
I am only Concerned on this

SELECT 1, 'ABC', '12B', '13B', '14B', '15B', '16B', '17B' FROM dual
UNION ALL
SELECT 2, 'BBC', '10V', 'VCF', 'BB', NULL, NULL, NULL FROM dual
UNION ALL
SELECT 3, 'ADD', '30', NULL, NULL, NULL, NULL, NULL FROM dual
Post #1352303
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse