1 2 3 32 Replies Latest reply on Feb 8, 2013 6:30 PM by user1584638 Go to original post
• ###### 15. Re: Loading the data
That is not possible.

Assuming by "record" you mean a row in the result set, you can't have rows with 10 columns and rows with 30 columns in the same result set.

Justin
• ###### 16. Re: Loading the data
So you saying if the source data is , BDF has same values upto some point plus it has extra

CIF Whole 74 DAY
CIF REVIEW
CIF Gate way
CIF Ball Park
CIF Ball game
CIF nobody
CIF every one

BDF Whole 74 DAY
BDF REVIEW
BDF Gate way
BDF Ball Park
BDF Ball game
BDF every one
BDF Some body
BDF Whole FOODS
BDF Ratings
BDF people So you are saying

CIF can not be captured with all 7 values as columns As first row
BDF can not be captured with all 10 values as columns as Second row ?
• ###### 17. Re: Loading the data
If you want a single result set with two rows given that data, both rows would need to have 11 columns. You could, of course, populate the last 3 columns of the CIF row with values of NULL. But those 3 columns would have to exist if you wanted them to exist for the BDF row.

Justin
• ###### 18. Re: Loading the data
Yes in real target table there 53 columns to accomadate all values, -- There will be no 54 value one row may have only 10 , other may have 30 , third may have 40 to max of 53

Thanks--Dileep
• ###### 19. Re: Loading the data
I'm not sure that I follow.

Are you now saying that you don't want to do a dynamic pivot? Are you saying that you now want to do a static pivot where the result set will always have 53 columns (meaning that there can be, at most, 52 rows in the table for each COLUMN1 value)?

Justin
• ###### 20. Re: Loading the data
1) Table is created with 53 columns

2) 1st set row may only 10 columns data
3) 2nd Set of row may have 30 columns of data
4) 3rd set row may have only 5 columns of data
5) 4th set row may have all 53 columns of data ( which will be maximum)

Please let me know, that will help
• ###### 21. Re: Loading the data
You didn't say what version of Oracle you have, so I won't tell you what version of Oracle is needed for this code. Now we're even.
WITH DATA(K, V) AS (SELECT
'ICF','RESUB' FROM DUAL UNION ALL SELECT
'DDF','RESF' FROM DUAL UNION ALL SELECT
'ICF','RESUB PA' FROM DUAL UNION ALL SELECT
'DDF',NULL FROM DUAL UNION ALL SELECT
'ICF','RESUB PB' FROM DUAL UNION ALL SELECT
'DDF','RESUB PF' FROM DUAL UNION ALL SELECT
'ICF','901 RESPONSE' FROM DUAL UNION ALL SELECT
'DDF','_' FROM DUAL UNION ALL SELECT
'ICF','901 APPEAl' FROM DUAL UNION ALL SELECT
'DDF','901 RES' FROM DUAL
)
select * from (
SELECT K, V, ROW_NUMBER() OVER(PARTITION BY K ORDER BY V) RN
FROM DATA
)
pivot(max(v) col for rn in(1,2,3,4,5,6,7,8,9));

K   1_COL        2_COL        3_COL        4_COL        5_COL        6_COL        7_COL        8_COL        9_COL
--- ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------
DDF 901 RES      RESF         RESUB PF     _
ICF 901 APPEAl   901 RESPONSE RESUB        RESUB PA     RESUB PB
To get more columns, just add ",10,11,12" and so on.
• ###### 22. Re: Loading the data
I'm still having a great deal of difficulty understanding.

As we've discussed, a result set must have a fixed number of columns. Every row in the result set must have exactly the same number of columns. You cannot have a result set where one row has 10 columns and another has 30. That simply isn't possible in SQL. Just like all the rows in a table must have the same number of columns.
1) Table is created with 53 columns
I'm assuming that this table is the destination for your data and is different than the initial two-column table. If so, then it sounds like you want to do a static pivot rather than a dynamic pivot. Did you look through the link I gave you yesterday?

Justin
• ###### 23. Re: Loading the data
Justin,

1) I checked with DBAs, I was told it is 11g

2) if i run this sql

select * from (
SELECT ist column , 2nd column , ROW_NUMBER() OVER(PARTITION BY K ORDER BY 2nd column ) RN
FROM original table ----DATA
)
pivot(max( second column --v) col for rn in(1,2,3,4,5,6,7,8,9));

That should produce the result -- let me run and get back to you
• ###### 24. Re: Loading the data
Justin,

Thank you so much, I was just looking for that kind of format.

You made my day.

Your sql produced what I was looking for-- Thanks Again-- I appreciate
• ###### 25. Re: Loading the data
Justin,

Final question,

Since your query is producing exatly what I want -- can we insert the out put into target table or I should capture that to temp table ?-- -- if so what will be syntax ?

select * from (
SELECT K, V, ROW_NUMBER() OVER(PARTITION BY K ORDER BY V) RN
FROM DATA
)
pivot(max(v) col for rn in(1,2,3,4,5,6,7,8,9));

• ###### 26. Re: Loading the data
I wasn't going to say anything, but actually that code is from me, not Justin.

If you want to insert the result into a table, just say
INSERT INTO OUT_TABLE (col1,col2,col2,...)
select * from (
SELECT K, V, ROW_NUMBER() OVER(PARTITION BY K ORDER BY V) RN
FROM DATA
)
pivot(max(v) col for rn in(1,2,3,4,5,6,7,8,9));
Best regards,
Stew
P.S. First time someone confused me with an Oracle Ace! Thanks :)
• ###### 27. Re: Loading the data
Thank you so much Stew-- I appreciate

I hope I did not confuse you
• ###### 28. Re: Loading the data
Stew Ashton wrote:
P.S. First time someone confused me with an Oracle Ace! Thanks :)
Given your other excellent posts, I suspect it is not the last time someone will make that mistake.

Justin
• ###### 29. Re: Loading the data
Justin,

I never would have expected to receive such a wonderful compliment. Thank you so much.

Best regards,

Stew
1 2 3