This content has been marked as final. Show 32 replies
What version of Oracle are you using?
Tim Hall has a nice page that discusses various string aggregation techniques. Personally, I prefer the user-defined aggregate function unless you're on 11.2 and can use LISTAGG.
user1584638 wrote:I quick answer then.
I have quick question to ask
You are probably talking about pivot then. Numerous techniques are found in the forum FAQ.
My guess is that you may not a finite number of "IFC", and therefore you are looking for the dynamic variant (Warning: not going to be easy)
Justin as i mentioned earlier
Present table is like this
ICF RESUB PA
ICF RESUB PB
DDF RESUB PF
ICF 901 RESPONSE
ICF 901 APPEAl
DDF 901 RES
So my target table
Coulm1 column2 Coulm2 column3 Coulm4 column5
ICF RESUB RESUB PA RESUB PB 901 RESPONSE 901 APPEAl
DDF RESF RESUB PF 901 RES
You can see from above First has 5 values second one has only three values all in line
OK, so you do want all 5 values to be in the output (in your last update, your expected output only had 4 columns not 6).
Do you know that there will be at most 5 COLUMN2 values per COLUMN1 value (or some other fixed number)? If not, you'd need to look at Re: 4. How do I convert rows to columns?.
Probably it may be typo but to make it more clear
Column 1 as shown earlier like ICF may be reapeated 10 times but each time it reapts it will have uniquie value in second column
10 ICF rows will have 10 diiferent column2 values
so final table should be
Column1 Column1 Column1 Column1 Column1
ICF A B C D and so on
Sorry if i did not put it right way earlier
Are you saying that you know that a single COL1 value will appear at most 10 times (and thus that the result set will have exactly 11 columns)? Or are you saying that the COL1 value will appear an arbitrary and unknown number of times and so the number of columns in the result set will change?
Assuming you are talking about the latter, you'd need to look at dynamic pivoting. There are multiple examples in the FAQ entry I linked to earlier that discuss how to do this.
I guess I have not made it clear
Column 1 may apprear from 1 to 30 times in the present table. If it appreas 30 times in the present table means it has 30 values so final target table should be
1 + 30
ICF (1st Cloumn) + 30 values
If DDF appreas only 10 times in present table
target should be
DDF ( 1st Column) + 10 values
1 clomuns will be unique
Please let me know I made it easy ?
You want to pivot the data.
It is still not clear to me whether you want a static pivot, where the number of columns is known at compile time, or whether you want a dynamic pivot, where the number of columns is variable and determined at runtime. My guess is that you are looking to do a dynamic pivot. That's why I linked to information on doing a dynamic pivot.
You know that a result set has to have the same number of columns for each row, right? Assuming you expect to return multiple rows (which you do in your third post but not in any of your other examples), the number of columns in the result set will have to be the same for each row.
The FAQ entry that I linked to above walks through different approaches to doing a dynamic pivot. Have you gone through that link?
"result set has to have the same number of columns for each row, right? "
If ICF has 30 rows in the Source table means
ICF 1st column (First Row) + 30 columns it needs fill in target table
BBF has only 10 rows in source table
BBF first column (SecondRow) + 10 Columns of data needs to fii in
MNF has only 20 records
MNF first column (third row) + 20 columns needs to fillin
Each first value will not have same number of columns to fill in the Target table