How to write Transpose in Oracle Please Help

Question

How to write Transpose in Oracle Please Help

vp7986

SSC Enthusiast

Points: 167
More actions
August 29, 2012 at 2:20 pm

#393047

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

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lowell SSC Guru Points: 323518 More actions · Answer 2

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!

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

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

Phil Parkin SSC Guru Points: 246965 More actions · Answer 4

Gianluca Sartori (8/30/2012)
--snip
Tested on oracle 11.2.0.1.

Turncoat!

🙂

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

spaghettidba SSC Guru Points: 105732 More actions · Answer 5

🙂

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

-- Gianluca Sartori

vp7986 SSC Enthusiast Points: 167 More actions · Answer 6

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.

spaghettidba SSC Guru Points: 105732 More actions · Answer 7

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 8

Also, you didn't provide any table name. Is "source" the name of your table?

-- Gianluca Sartori

vp7986 SSC Enthusiast Points: 167 More actions · Answer 9

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 10

SELECT ID, NAME, "ADD"

FROM source

UNPIVOT ( "ADD" FOR col IN (ADD1, ADD2, ADD3, ADD4, ADD5, ADD6));

Given that your table is called source and has the columns (ID, NAME, ADD1, ADD2, ADD3, ADD4, ADD5, ADD6).

-- Gianluca Sartori