Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
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


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 2:04 PM
Points: 15,648, Visits: 15,875
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 @ 5:08 PM
Points: 14,330, Visits: 37,441
if you google "Oracle UNPIVOT", you should get some examples of this "transpose" thing you are looking for.

Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Post #1351919
Posted Thursday, August 30, 2012 6:58 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:46 AM
Points: 5,433, Visits: 12,911
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 @ 4:40 PM
Points: 7,081, Visits: 17,196
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.

Never attribute to malice that which is adequately explained by stupidity
Post #1352178
Posted Thursday, August 30, 2012 7:16 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:46 AM
Points: 5,433, Visits: 12,911

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


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:46 AM
Points: 5,433, Visits: 12,911
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


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:46 AM
Points: 5,433, Visits: 12,911
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