Hello Guru's I have quick question to ask
If i have data like this
ICF RESUB PA
ICF RESUB PB
ICF 1001 RESPONSE
ICF 1001 APPEAL
ICF is in the first cloumn and ICF related values are in in second column
So my task is to create all into one row
ICF RESUB RESUB PA RESUB PB 901 RESPONSE 901 APPEAL REMS MOD ORD ( ICF is like parent only one time it should present)
I appreciate if same can be loaded with PL?SQL
Thanks In advance
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.
How do you know which three values to use in the three columns you want? How do you know that you don't want the rows with data values of 1001 RESPONSE or ICF 1001 APPEAL to appear in the output?
I quick answer then.
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.
Yes it will be Dynamic because
ICF may have 30 values in second coulmn
BBF may have only 10 Values in second coulmn
CDF may have 40 values in second coulm
so final target should be
ICF (First Row) + 30 columns
BBF ( second row ) + 10 columns
CDF ( third row) + 40 columns
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