Thanks
January 27, 2004 - 1pm Central time zone
Reviewer: Gaurav Srivastava from London, UK
Many thanks Tom, this is what was required.
January 11, 2005 - 12pm Central time zone
Reviewer: Mala from Aurora, IL USA
Tom, Thanks for the excellent forum. Its has been most helpful. I have a question..
I am using pivoting as demonstrated by you to transform rows into columns however the data is
offset in each column by a row.. How can i fix this? THis example has only 2 rows of data. Actual
data will be of 49, 75 or 121 measurement points on each wafer..
Here is Original data that i pulled into an external table.
<Begin>
Recipe: Diam 5mm exc
LotID: s4012926
WaferID: 1
Slot: 1
Probe: 1.6 mil Tip - Type A
Operator:
Shift:
Equipment:
Process:
Comment:
Processed: 2/3/2004 15:26
Collected: 2/3/2004 15:52
Test Pattern: Diameter Scan, 49-Site
Units: Ang
Initial Temperature: 19.4
Final Temperature: 19.53
TCR Name: <none>
TCR Value: 0
Correlation: Y=S1/S2 * A + BX' + CX'^2 + DX'^3
S1: 1
S2: 1
A: 0
B: 180
C: 0
D: 0
...
Pivoted like
INSERT INTO RS100_SUMMARY (RECIPE, LOTID, WAFERID, SLOT, PROBE,
OPERATOR, SHIFT, EQUIPMENT, PROCESS, COMMENTS, PROCESSED_TIME,
COLLECTED_TIME, TEST_PATTERN, UNITS, INITIAL_TEMPERATURE,
FINAL_TEMPERATURE, TCR_NAME, TCT_VALUE, DATA_POINTS, GOOD_DATA_POINTS,
MEAN, STDV, MIN, MAX, RANGE, S1, S2, A, B, C, D, X_TICK, Y_TICK)
SELECT
-- measurement_desc , reading,
MAX (DECODE (measurement_desc, 'Recipe:', reading,NULL)) Recipe
,MAX (DECODE (measurement_desc, 'LotID:', reading,NULL))LotID
,MAX (DECODE (measurement_desc, 'WaferID:', reading,NULL)) WaferID
,MAX (DECODE (measurement_desc, 'Slot:', reading,NULL)) Slot
,MAX (DECODE (measurement_desc, 'Probe:', reading,NULL))Probe
,MAX (DECODE (measurement_desc, 'Operator:', reading,NULL)) Operator
,MAX (DECODE (measurement_desc, 'Shift:', reading,NULL))Shift
,MAX (DECODE (measurement_desc, 'Equipment:', reading,NULL)) Equipment
,MAX (DECODE (measurement_desc, 'Process:', reading,NULL)) Process
,MAX (DECODE (measurement_desc, 'Comment:',reading,NULL)) COMMENTS
,MAX (DECODE (measurement_desc, 'Processed:', TO_DATE(reading, 'mm/dd/yyyy HH24:mi'),NULL))
Processed
,MAX (DECODE (measurement_desc, 'Collected:', TO_DATE(reading, 'mm/dd/yyyy HH24:mi'),NULL))
Collected
,MAX (DECODE (measurement_desc, 'Test Pattern:', reading,NULL))TestPattern
,MAX (DECODE (measurement_desc, 'Units:', reading,NULL)) Units
,MAX (DECODE (measurement_desc, 'INITIAL Temperature:', reading,NULL)) INITIALTemperature
,MAX (DECODE (measurement_desc, 'Final Temperature:', reading,NULL)) FinalTemperature
,MAX (DECODE (measurement_desc, 'TCR Name:', reading,NULL)) TCRName
,MAX (DECODE (measurement_desc, 'TCR Value:', reading,NULL))TCRValue
,MAX (DECODE (measurement_desc, 'Data Points:', reading,NULL)) DataPoints
,MAX (DECODE (measurement_desc, 'Good Points:', reading,NULL)) goodPoints
,MAX (DECODE (measurement_desc, 'Mean:', reading,NULL)) Mean
,MAX (DECODE (measurement_desc, 'Stdv:', reading,NULL)) Stdv
,MAX (DECODE (measurement_desc, 'MIN:', reading,NULL)) MIN
,MAX (DECODE (measurement_desc, 'MAX:', reading,NULL)) MAX
,MAX (DECODE (measurement_desc, 'RANGE:', reading,NULL)) "RANGE"
,MAX (DECODE (measurement_desc, 'S1:', reading,NULL)) S1
,MAX (DECODE (measurement_desc, 'S2:', reading,NULL)) S2
,MAX (DECODE (measurement_desc, 'A:' , reading,NULL)) A
,MAX (DECODE (measurement_desc, 'B:', reading,NULL))B
,MAX (DECODE (measurement_desc, 'C:', reading,NULL)) C
,MAX (DECODE (measurement_desc, 'D:', reading,NULL))D
,MAX (DECODE (measurement_desc, 'X:', reading,NULL)) X
,MAX (DECODE (measurement_desc, 'Y:', reading,NULL))Y
FROM RS100_POST_EXT
WHERE measurement_desc NOT LIKE 'Site%'
GROUP BY ROWNUM, measurement_desc, reading
COMMIT;
Here is the pivoted data
RECIPE LOTID WAFERID SLOT PROBE
-------------------------------------------------- --------------- --------------- ----------
-------------------------------
Diam 5mm exc
s4020247
12
2
1.6
mil Tip - Type A
I need the data to look like this a column in the same row for each lotid, waferid, slot
combination.
RECIPE LOTID WAFERID SLOT PROBE ...more columns....
Diam 5mm exc s4020247 12 2 1.6 mil Tip - TypeA .. ..
Thanks
Followup January 11, 2005 - 1pm Central time zone:
why are you grouping by rownum?
January 11, 2005 - 3pm Central time zone
Reviewer: Mala from Aurora, IL USA
If I dont group by rownum the data sets get mixed up. Column values for different data sets are
displayed together
We have set of summary data followed by detail which I have split into master and detail tables in
the database. By retaining the original form I will be able link them (master-detail)properly..is
my thinking.
I have copied as eg. 3 sets of raw data..
MEASUREMENT_DESC READING RECIPE LOTID WAFERID SLOT PROBE OPERATOR SHIFT
EQUIPMENT PROCESS COMMENTS PROCESSED COLLECTED TESTPATTERN UNITS
INITIALTEMPERATURE FINALTEMPERATURE TCRNAME TCRVALUE DATAPOINTS GOODPOINTS MEAN
STDV MIN MAX RANGE S1 S2 A B C D X Y
A: 0
0
B: 180
180
C: 0
0
D: 0
0
S1: 1
1
S2: 1
1
X': 1/X
Y': Y
Max: 10210.37851
Max: 15076.89628
Min: 13305.11518
Min: 8110.022686
Mean: 9723.01967
9723.01967
Mean: 14766.02225
14766.02225
Slot: 1 1
Slot: 2 2
Stdv: 308.852554
308.852554
Stdv: 444.282933
444.282933
LotID: s4020247 s4020247
Probe: 1.6 mil Tip - Type A 1.6 mil Tip - Type A
Range: 2100.35582
Range: 1771.781104
Shift:
Units: Ang Ang
<Begin>
Recipe: Diam 5mm exc Diam 5mm exc
Comment:
Process:
WaferID: 11 11
WaferID: 12 12
Operator:
TCR Name: <none> <none>
Collected: 2/25/2004 16:38 2/25/2004 4:38:00 PM
Collected: 2/25/2004 16:39 2/25/2004 4:39:00 PM
Equipment:
Processed: 2/25/2004 16:35
DATA SET:
Here is how the csv file looks
<Begin>
Recipe: Diam 5mm exc
LotID: s4012926
WaferID: 1
Slot: 1
Probe: 1.6 mil Tip - Type A
Operator:
Shift:
Equipment:
Process:
Comment:
Processed: 2/3/2004 15:26
Collected: 2/3/2004 15:52
Test Pattern: Diameter Scan, 49-Site
Units: Ang
Initial Temperature: 19.4
Final Temperature: 19.53
TCR Name: <none>
TCR Value: 0
Correlation: Y=S1/S2 * A + BX' + CX'^2 + DX'^3
S1: 1
S2: 1
A: 0
B: 180
C: 0
D: 0
X': 1/X
Y': Y
Current used: 127204.9228
Data Points: 49
Good Data Points: 49
Mean: 16614.03523
Stdv: 214.582959
Min: 16075.49246
Max: 16887.92103
Range: 812.428568
Site Processed Radius Theta X Y Raw Rs Status
Site 1: 16887.92103 95 270 -95 0 0.010659 Valid Measurement
Site 2: 16080.20591 91.041635 270 -91.041635 0 0.011194 Valid Measurement
Site 3: 16268.99624 87.083365 270 -87.083365 0 0.011064 Valid Measurement
Site 4: 16388.02615 83.125 270 -83.125 0 0.010984 Valid Measurement
Site 5: 16511.49997 79.166635 270 -79.166635 0 0.010901 Valid Measurement
Site 6: 16562.98184 75.208365 270 -75.208365 0 0.010868 Valid Measurement
Site 7: 16677.62823 71.25 270 -71.25 0 0.010793 Valid Measurement
Site 8: 16725.2676 67.291635 270 -67.291635 0 0.010762 Valid Measurement
Site 9: 16763.89752 63.333365 270 -63.333365 0 0.010737 Valid Measurement
Site 10: 16838.34854 59.375 270 -59.375 0 0.01069 Valid Measurement
Site 11: 16863.95693 55.416635 270 -55.416635 0 0.010674 Valid Measurement
Site 12: 16860.61769 51.458365 270 -51.458365 0 0.010676 Valid Measurement
Site 13: 16873.43403 47.5 270 -47.5 0 0.010668 Valid Measurement
Site 14: 16853.26553 43.541635 270 -43.541635 0 0.01068 Valid Measurement
Site 15: 16852.38739 39.583365 270 -39.583365 0 0.010681 Valid Measurement
Site 16: 16798.2385 35.625 270 -35.625 0 0.010715 Valid Measurement
Site 17: 16763.5619 31.666635 270 -31.666635 0 0.010738 Valid Measurement
Site 18: 16719.12616 27.708365 270 -27.708365 0 0.010766 Valid Measurement
Site 19: 16650.73537 23.75 270 -23.75 0 0.01081 Valid Measurement
Site 20: 16589.39811 19.791635 270 -19.791635 0 0.01085 Valid Measurement
Site 21: 16532.64679 15.833365 270 -15.833365 0 0.010888 Valid Measurement
Site 22: 16492.24666 11.875 270 -11.875 0 0.010914 Valid Measurement
Site 23: 16481.13848 7.916635 270 -7.916635 0 0.010922 Valid Measurement
Site 24: 16411.53215 3.958365 270 -3.958365 0 0.010968 Valid Measurement
Site 25: 16343.19825 0 90 0 0 0.011014 Valid Measurement
Site 26: 16395.72617 3.958365 90 3.958365 0 0.010978 Valid Measurement
Site 27: 16444.02546 7.916635 90 7.916635 0 0.010946 Valid Measurement
Site 28: 16493.56185 11.875 90 11.875 0 0.010913 Valid Measurement
Site 29: 16531.34928 15.833365 90 15.833365 0 0.010888 Valid Measurement
Site 30: 16596.5521 19.791635 90 19.791635 0 0.010846 Valid Measurement
Site 31: 16667.85621 23.75 90 23.75 0 0.010799 Valid Measurement
Site 32: 16687.72894 27.708365 90 27.708365 0 0.010786 Valid Measurement
Site 33: 16736.1988 31.666635 90 31.666635 0 0.010755 Valid Measurement
Site 34: 16764.84371 35.625 90 35.625 0 0.010737 Valid Measurement
Site 35: 16796.94947 39.583365 90 39.583365 0 0.010716 Valid Measurement
Site 36: 16837.45619 43.541635 90 43.541635 0 0.01069 Valid Measurement
Site 37: 16838.98362 47.5 90 47.5 0 0.010689 Valid Measurement
Site 38: 16808.82764 51.458365 90 51.458365 0 0.010709 Valid Measurement
Site 39: 16793.93182 55.416635 90 55.416635 0 0.010718 Valid Measurement
Site 40: 16763.46114 59.375 90 59.375 0 0.010738 Valid Measurement
Site 41: 16705.45007 63.333365 90 63.333365 0 0.010775 Valid Measurement
Site 42: 16644.4755 67.291635 90 67.291635 0 0.010814 Valid Measurement
Site 43: 16582.26304 71.25 90 71.25 0 0.010855 Valid Measurement
Site 44: 16495.78318 75.208365 90 75.208365 0 0.010912 Valid Measurement
Site 45: 16403.08225 79.166635 90 79.166635 0 0.010974 Valid Measurement
Site 46: 16307.64586 83.125 90 83.125 0 0.011038 Valid Measurement
Site 47: 16161.88999 87.083365 90 87.083365 0 0.011137 Valid Measurement
Site 48: 16075.49246 91.041635 90 91.041635 0 0.011197 Valid Measurement
Site 49: 16763.9348 95 90 95 0 0.010737 Valid Measurement
<Begin>
Recipe: Diam 5mm exc
LotID: s4012926
WaferID: 2
Slot: 2
Probe: 1.6 mil Tip - Type A
Operator:
Shift:
Equipment:
Process:
Comment:
Processed: 2/3/2004 15:26
Collected: 2/3/2004 15:54
Test Pattern: Diameter Scan, 49-Site
Units: Ang
Initial Temperature: 19.42
Final Temperature: 19.54
TCR Name: <none>
TCR Value: 0
Correlation: Y=S1/S2 * A + BX' + CX'^2 + DX'^3
S1: 1
S2: 1
A: 0
B: 180
C: 0
D: 0
X': 1/X
Y': Y
Current used: 125770.5626
Data Points: 49
Good Data Points: 49
Mean: 16543.59685
Stdv: 280.774239
Min: 15804.50898
Max: 16996.90008
Range: 1192.391105
Site Processed Radius Theta X Y Raw Rs Status
Site 1: 16736.00724 95 270 -95 0 0.010755 Valid Measurement
Site 2: 15804.50898 91.041635 270 -91.041635 0 0.011389 Valid Measurement
Site 3: 16024.07999 87.083365 270 -87.083365 0 0.011233 Valid Measurement
Site 4: 16183.34917 83.125 270 -83.125 0 0.011123 Valid Measurement
Site 5: 16341.45275 79.166635 270 -79.166635 0 0.011015 Valid Measurement
Site 6: 16437.73016 75.208365 270 -75.208365 0 0.01095 Valid Measurement
Site 7: 16544.92021 71.25 270 -71.25 0 0.010879 Valid Measurement
Site 8: 16646.62458 67.291635 270 -67.291635 0 0.010813 Valid Measurement
Site 9: 16708.39067 63.333365 270 -63.333365 0 0.010773 Valid Measurement
Site 10: 16758.24991 59.375 270 -59.375 0 0.010741 Valid Measurement
Site 11: 16798.28582 55.416635 270 -55.416635 0 0.010715 Valid Measurement
Site 12: 16829.20186 51.458365 270 -51.458365 0 0.010696 Valid Measurement
Site 13: 16803.51295 47.5 270 -47.5 0 0.010712 Valid Measurement
Site 14: 16774.57361 43.541635 270 -43.541635 0 0.010731 Valid Measurement
Site 15: 16691.86288 39.583365 270 -39.583365 0 0.010784 Valid Measurement
Site 16: 16996.90008 35.625 270 -35.625 0 0.01059 Valid Measurement
Site 17: 16914.51541 31.666635 270 -31.666635 0 0.010642 Valid Measurement
Site 18: 16829.55804 27.708365 270 -27.708365 0 0.010695 Valid Measurement
Site 19: 16773.64768 23.75 270 -23.75 0 0.010731 Valid Measurement
Site 20: 16675.95183 19.791635 270 -19.791635 0 0.010794 Valid Measurement
Site 21: 16579.69729 15.833365 270 -15.833365 0 0.010857 Valid Measurement
Site 22: 16473.88849 11.875 270 -11.875 0 0.010926 Valid Measurement
Site 23: 16386.44183 7.916635 270 -7.916635 0 0.010985 Valid Measurement
Site 24: 16297.20474 3.958365 270 -3.958365 0 0.011045 Valid Measurement
Site 25: 16251.85088 0 90 0 0 0.011076 Valid Measurement
Site 26: 16284.40727 3.958365 90 3.958365 0 0.011054 Valid Measurement
Site 27: 16341.76548 7.916635 90 7.916635 0 0.011015 Valid Measurement
Site 28: 16389.54172 11.875 90 11.875 0 0.010983 Valid Measurement
Site 29: 16451.60413 15.833365 90 15.833365 0 0.010941 Valid Measurement
Site 30: 16526.71662 19.791635 90 19.791635 0 0.010891 Valid Measurement
Site 31: 16579.22035 23.75 90 23.75 0 0.010857 Valid Measurement
Site 32: 16635.27173 27.708365 90 27.708365 0 0.01082 Valid Measurement
Site 33: 16687.85099 31.666635 90 31.666635 0 0.010786 Valid Measurement
Site 34: 16745.70256 35.625 90 35.625 0 0.010749 Valid Measurement
Site 35: 16771.43857 39.583365 90 39.583365 0 0.010733 Valid Measurement
Site 36: 16799.45226 43.541635 90 43.541635 0 0.010715 Valid Measurement
Site 37: 16785.04147 47.5 90 47.5 0 0.010724 Valid Measurement
Site 38: 16789.0083 51.458365 90 51.458365 0 0.010721 Valid Measurement
Site 39: 16758.37457 55.416635 90 55.416635 0 0.010741 Valid Measurement
Site 40: 16729.9727 59.375 90 59.375 0 0.010759 Valid Measurement
Site 41: 16672.52909 63.333365 90 63.333365 0 0.010796 Valid Measurement
Site 42: 16585.31765 67.291635 90 67.291635 0 0.010853 Valid Measurement
Site 43: 16487.99029 71.25 90 71.25 0 0.010917 Valid Measurement
Site 44: 16375.02262 75.208365 90 75.208365 0 0.010992 Valid Measurement
Site 45: 16291.88942 79.166635 90 79.166635 0 0.011048 Valid Measurement
Site 46: 16166.72034 83.125 90 83.125 0 0.011134 Valid Measurement
Site 47: 15965.06341 87.083365 90 87.083365 0 0.011275 Valid Measurement
Site 48: 15806.1996 91.041635 90 91.041635 0 0.011388 Valid Measurement
Site 49: 16747.73726 95 90 95 0 0.010748 Valid Measurement
<Begin>
Recipe: Diam 5mm exc
LotID: s4012926
WaferID: 3
Slot: 3
Probe: 1.6 mil Tip - Type A
Operator:
Shift:
Equipment:
Process:
Comment:
Processed: 2/3/2004 15:26
Collected: 2/3/2004 15:56
Test Pattern: Diameter Scan, 49-Site
Units: Ang
Initial Temperature: 19.46
Final Temperature: 19.58
TCR Name: <none>
TCR Value: 0
Correlation: Y=S1/S2 * A + BX' + CX'^2 + DX'^3
S1: 1
S2: 1
A: 0
B: 180
C: 0
D: 0
X': 1/X
Y': Y
Current used: 125477.9846
Data Points: 49
Good Data Points: 49
Mean: 16431.81874
Stdv: 308.12566
Min: 15658.49856
Max: 16883.92836
Range: 1225.429806
Site Processed Radius Theta X Y Raw Rs Status
Site 1: 16499.27253 95 270 -95 0 0.01091 Valid Measurement
Site 2: 15658.49856 91.041635 270 -91.041635 0 0.011495 Valid Measurement
Site 3: 15886.39359 87.083365 270 -87.083365 0 0.01133 Valid Measurement
Site 4: 16107.02482 83.125 270 -83.125 0 0.011175 Valid Measurement
Site 5: 16261.15782 79.166635 270 -79.166635 0 0.011069 Valid Measurement
Site 6: 16360.27303 75.208365 270 -75.208365 0 0.011002 Valid Measurement
Site 7: 16464.99665 71.25 270 -71.25 0 0.010932 Valid Measurement
Site 8: 16563.13264 67.291635 270 -67.291635 0 0.010868 Valid Measurement
Site 9: 16645.3572 63.333365 270 -63.333365 0 0.010814 Valid Measurement
Site 10: 16685.54234 59.375 270 -59.375 0 0.010788 Valid Measurement
Site 11: 16737.08903 55.416635 270 -55.416635 0 0.010755 Valid Measurement
Site 12: 16756.20701 51.458365 270 -51.458365 0 0.010742 Valid Measurement
Site 13: 16745.74625 47.5 270 -47.5 0 0.010749 Valid Measurement
Site 14: 16704.33964 43.541635 270 -43.541635 0 0.010776 Valid Measurement
Site 15: 16630.23179 39.583365 270 -39.583365 0 0.010824 Valid Measurement
Site 16: 16883.92836 35.625 270 -35.625 0 0.010661 Valid Measurement
Site 17: 16857.3055 31.666635 270 -31.666635 0 0.010678 Valid Measurement
Site 18: 16728.32899 27.708365 270 -27.708365 0 0.01076 Valid Measurement
Site 19: 16640.86881 23.75 270 -23.75 0 0.010817 Valid Measurement
Site 20: 16575.38339 19.791635 270 -19.791635 0 0.010859 Valid Measurement
Site 21: 16438.12688 15.833365 270 -15.833365 0 0.01095 Valid Measurement
Site 22: 16306.51974 11.875 270 -11.875 0 0.011039 Valid Measurement
Site 23: 16198.77256 7.916635 270 -7.916635 0 0.011112 Valid Measurement
Site 24: 16087.08175 3.958365 270 -3.958365 0 0.011189 Valid Measurement
Site 25: 16032.65794 0 90 0 0 0.011227 Valid Measurement
Site 26: 16073.92882 3.958365 90 3.958365 0 0.011198 Valid Measurement
Site 27: 16144.94533 7.916635 90 7.916635 0 0.011149 Valid Measurement
Site 28: 16193.52199 11.875 90 11.875 0 0.011116 Valid Measurement
Site 29: 16300.62405 15.833365 90 15.833365 0 0.011043 Valid Measurement
Site 30: 16382.59835 19.791635 90 19.791635 0 0.010987 Valid Measurement
Site 31: 16469.48003 23.75 90 23.75 0 0.010929 Valid Measurement
Site 32: 16512.12212 27.708365 90 27.708365 0 0.010901 Valid Measurement
Site 33: 16594.59204 31.666635 90 31.666635 0 0.010847 Valid Measurement
Site 34: 16636.21235 35.625 90 35.625 0 0.01082 Valid Measurement
Site 35: 16694.52102 39.583365 90 39.583365 0 0.010782 Valid Measurement
Site 36: 16742.35281 43.541635 90 43.541635 0 0.010751 Valid Measurement
Site 37: 16710.24912 47.5 90 47.5 0 0.010772 Valid Measurement
Site 38: 16724.34937 51.458365 90 51.458365 0 0.010763 Valid Measurement
Site 39: 16704.64099 55.416635 90 55.416635 0 0.010775 Valid Measurement
Site 40: 16672.2926 59.375 90 59.375 0 0.010796 Valid Measurement
Site 41: 16606.52694 63.333365 90 63.333365 0 0.010839 Valid Measurement
Site 42: 16506.18814 67.291635 90 67.291635 0 0.010905 Valid Measurement
Site 43: 16382.50474 71.25 90 71.25 0 0.010987 Valid Measurement
Site 44: 16292.12688 75.208365 90 75.208365 0 0.011048 Valid Measurement
Site 45: 16151.29101 79.166635 90 79.166635 0 0.011145 Valid Measurement
Site 46: 16027.89372 83.125 90 83.125 0 0.01123 Valid Measurement
Site 47: 15849.82679 87.083365 90 87.083365 0 0.011357 Valid Measurement
Site 48: 15671.57914 91.041635 90 91.041635 0 0.011486 Valid Measurement
Site 49: 16660.5129 95 90 95 0 0.010804 Valid Measurement
<Begin>
Recipe: Diam 5mm exc
LotID: s4012926
WaferID: 4
Slot: 4
Probe: 1.6 mil Tip - Type A
Operator:
Shift:
Equipment:
Process:
Comment:
Processed: 2/3/2004 15:26
Collected: 2/3/2004 15:57
Test Pattern: Diameter Scan, 49-Site
Units: Ang
Initial Temperature: 19.49
Final Temperature: 19.61
TCR Name: <none>
TCR Value: 0
Correlation: Y=S1/S2 * A + BX' + CX'^2 + DX'^3
S1: 1
S2: 1
A: 0
B: 180
C: 0
D: 0
X': 1/X
Y': Y
Current used: 123062.0409
Data Points: 49
Good Data Points: 49
Mean: 16280.48665
Stdv: 337.669634
Min: 15410.83989
Max: 16799.21754
Range: 1388.377648
Site Processed Radius Theta X Y Raw Rs Status
Site 1: 16521.79761 95 270 -95 0 0.010895 Valid Measurement
Site 2: 15532.00515 91.041635 270 -91.041635 0 0.011589 Valid Measurement
Site 3: 15791.07203 87.083365 270 -87.083365 0 0.011399 Valid Measurement
Site 4: 16031.63133 83.125 270 -83.125 0 0.011228 Valid Measurement
Site 5: 16184.05092 79.166635 270 -79.166635 0 0.011122 Valid Measurement
Site 6: 16299.07893 75.208365 270 -75.208365 0 0.011044 Valid Measurement
Site 7: 16382.76899 71.25 270 -71.25 0 0.010987 Valid Measurement
Site 8: 16449.87101 67.291635 270 -67.291635 0 0.010942 Valid Measurement
Site 9: 16522.47716 63.333365 270 -63.333365 0 0.010894 Valid Measurement
Site 10: 16457.59779 59.375 270 -59.375 0 0.010937 Valid Measurement
Site 11: 16799.21754 55.416635 270 -55.416635 0 0.010715 Valid Measurement
Site 12: 16786.47309 51.458365 270 -51.458365 0 0.010723 Valid Measurement
Site 13: 16739.44143 47.5 270 -47.5 0 0.010753 Valid Measurement
Site 14: 16700.46183 43.541635 270 -43.541635 0 0.010778 Valid Measurement
Site 15: 16647.88475 39.583365 270 -39.583365 0 0.010812 Valid Measurement
Site 16: 16578.82791 35.625 270 -35.625 0 0.010857 Valid Measurement
Site 17: 16498.16383 31.666635 270 -31.666635 0 0.01091 Valid Measurement
Site 18: 16441.66248 27.708365 270 -27.708365 0 0.010948 Valid Measurement
Site 19: 16346.85266 23.75 270 -23.75 0 0.011011 Valid Measurement
Site 20: 16250.10328 19.791635 270 -19.791635 0 0.011077 Valid Measurement
Site 21: 16112.02886 15.833365 270 -15.833365 0 0.011172 Valid Measurement
Site 22: 16017.83101 11.875 270 -11.875 0 0.011237 Valid Measurement
Site 23: 15977.47417 7.916635 270 -7.916635 0 0.011266 Valid Measurement
Site 24: 15836.93127 3.958365 270 -3.958365 0 0.011366 Valid Measurement
Site 25: 15762.77165 0 90 0 0 0.011419 Valid Measurement
Site 26: 15861.28023 3.958365 90 3.958365 0 0.011348 Valid Measurement
Site 27: 15908.02568 7.916635 90 7.916635 0 0.011315 Valid Measurement
Site 28: 15959.48903 11.875 90 11.875 0 0.011279 Valid Measurement
Site 29: 16052.86544 15.833365 90 15.833365 0 0.011213 Valid Measurement
Site 30: 16147.3039 19.791635 90 19.791635 0 0.011147 Valid Measurement
Site 31: 16267.86687 23.75 90 23.75 0 0.011065 Valid Measurement
Site 32: 16363.54346 27.708365 90 27.708365 0 0.011 Valid Measurement
Site 33: 16426.98104 31.666635 90 31.666635 0 0.010958 Valid Measurement
Site 34: 16520.53156 35.625 90 35.625 0 0.010896 Valid Measurement
Site 35: 16570.3787 39.583365 90 39.583365 0 0.010863 Valid Measurement
Site 36: 16611.5956 43.541635 90 43.541635 0 0.010836 Valid Measurement
Site 37: 16608.57914 47.5 90 47.5 0 0.010838 Valid Measurement
Site 38: 16620.50902 51.458365 90 51.458365 0 0.01083 Valid Measurement
Site 39: 16593.1045 55.416635 90 55.416635 0 0.010848 Valid Measurement
Site 40: 16562.06049 59.375 90 59.375 0 0.010868 Valid Measurement
Site 41: 16495.23852 63.333365 90 63.333365 0 0.010912 Valid Measurement
Site 42: 16419.56563 67.291635 90 67.291635 0 0.010963 Valid Measurement
Site 43: 16325.89487 71.25 90 71.25 0 0.011025 Valid Measurement
Site 44: 16207.21957 75.208365 90 75.208365 0 0.011106 Valid Measurement
Site 45: 16068.43844 79.166635 90 79.166635 0 0.011202 Valid Measurement
Site 46: 15918.18563 83.125 90 83.125 0 0.011308 Valid Measurement
Site 47: 15696.48028 87.083365 90 87.083365 0 0.011468 Valid Measurement
Site 48: 15410.83989 91.041635 90 91.041635 0 0.01168 Valid Measurement
Site 49: 16459.3915 95 90 95 0 0.010936 Valid Measurement
for 3 sets of data (3 wafers from a lot, with 49 measurement sites)
Hope that explains it.
Followup January 11, 2005 - 4pm Central time zone:
and if you group by rownum, nothing get "squashed"
we don't need pages and pages of data -- but do you conceptually understand how the pivot works?
You need a set of columns to pivot on -- do you have such a set, the introduce of rownum in there
indicates to me either
a) you haven't conceptually understood the query yet
b) you don't have anything to actually pivot on and you cannot get there from here.
January 11, 2005 - 4pm Central time zone
Reviewer: Mala
Thanks for your quick response. Sorry about all the data. I was hoping it could give you a better
picture.
You are right I have not used pivot queries before and by browsing here it looked like could
provide the solution..
BTW i just got your book Expert one-on-one and I will review it more.
1. I do need all the rows in the summary part of the data to be queryable columns in the table.
2. from there I need create the detail table data with the site measurements information
could you please suggest a good approach?
Regards
Followup January 11, 2005 - 4pm Central time zone:
seems like these are your pivot columns:
each lotid,
waferid, slot combination.
select lotid, waferid, slot, max( decode( .... ) ), max( decode( ... ) )
from table
group by lotid, waferid, slot
/
perhaps
January 11, 2005 - 5pm Central time zone
Reviewer: Mala
Tom,
The only columns I have are 'measurement_desc' and the 'reading' in the external table.
Followup January 11, 2005 - 7pm Central time zone:
oh, oh oh i see.... tricky tricky....
got it, not a problem, i took your data and duped it for 3 groups (2cd and 3rd observations)
ps$tkyte@ORA9IR2> drop table et;
Table dropped.
ops$tkyte@ORA9IR2> create table et
2 (text varchar2(255))
3 ORGANIZATION EXTERNAL
4 ( type oracle_loader
5 default directory data_dir
6 access parameters
7 ( fields terminated by ',' )
8 location ('et.dat')
9 )
10 /
Table created.
ops$tkyte@ORA9IR2> column label format a10
ops$tkyte@ORA9IR2> column data format a40
ops$tkyte@ORA9IR2> column lotid format a10
ops$tkyte@ORA9IR2> column waferid format a10
ops$tkyte@ORA9IR2> column slot format a10
ops$tkyte@ORA9IR2> column probe format a10
ops$tkyte@ORA9IR2> column tcr_name format a10
ops$tkyte@ORA9IR2> column units format a10
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select grp,
2 max(decode(label,'LotID', data)) LotID,
3 max(decode(label,'WaferID', data)) WaferID,
4 max(decode(label,'Slot', data)) Slot,
5 max(decode(label,'Probe', data)) Probe,
6 max(decode(label,'Units', data)) Units,
7 max(decode(label,'TCR Name', data)) TCR_name
8 from (
9 select r, label, data,
10 max(grp) over (order by r) grp
11 from (
12 select r, label, data,
13 case when lead(label) over (order by r) = 'LotID'
14 then r
15 end grp
16 from (
17 select rownum r,
18 trim(substr(text,1,instr(text,':')-1)) label,
19 trim(substr(text,instr(text,':')+1 )) data
20 from et
21 )
22 )
23 )
24 group by grp
25 /
GRP LOTID WAFERID SLOT PROBE UNITS TCR_NAME
---------- ---------- ---------- ---------- ---------- ---------- ----------
1 s4012926 1 1 1.6 mil Ti Ang <none>
p - Type A
26 s4012926 2 1 2cd obse 1 2cd obse 1.6 mil Ti Ang 2cd ob <none> 2cd
cd observa rvation rvation p - Type A servation observati
tion 2cd obser on
vation
51 s4012926 3 1 3rd obse 1 3rd obse 1.6 mil Ti Ang 3rd ob <none> 3rd
rd observa rvation rvation p - Type A servation observati
tion 3rd obser on
vation
obviously.... :)
a)
17 select rownum r,
18 trim(substr(text,1,instr(text,':')-1)) label,
19 trim(substr(text,instr(text,':')+1 )) data
20 from et
read the data, parse it into "label", "data" and assign rownum so we can order later...
b)
12 select r, label, data,
13 case when lead(label) over (order by r) = 'LotID'
14 then r
15 end grp
16 from (
17 select rownum r,
18 trim(substr(text,1,instr(text,':')-1)) label,
19 trim(substr(text,instr(text,':')+1 )) data
20 from et
21 )
when the next row is LotID -- we know this row is the beginning of a "group" -- output a marker so
we can...
c)
carry it down (technique 123423 outlined in analytics to the rescue:
http://www.oracle.com/technology/oramag/oracle/04-mar/o24asktom.html
9 select r, label, data,
10 max(grp) over (order by r) grp
11 from (
12 select r, label, data,
13 case when lead(label) over (order by r) = 'LotID'
14 then r
15 end grp
16 from (
17 select rownum r,
18 trim(substr(text,1,instr(text,':')-1)) label,
19 trim(substr(text,instr(text,':')+1 )) data
20 from et
21 )
22 )
d) and then simply pivot each group....
Most Brilliant!!!
January 12, 2005 - 1pm Central time zone
Reviewer: Mala
Tom,
That was amazing..!!! You are a genius! It works just like I wanted. I am still trying to grasp
the analytics functions.. Your explanations were very helpful.
Thank you so much for your time and this forum.. Great work
With lots of Regards
ROWS INTO COLUMNS
January 24, 2005 - 10am Central time zone
Reviewer: Fei from Chile
Hi, It was a really good answer, and I try to use it, but I'm working in sql server, and It seems
it doesn't support decode funcition or create | replace packages, so I'm stuck.
I tried to understand max(decode (...)) functions, I mean the logic and the syntax to transform
rows into columns, because I 've never used those functions, and I think I just find out how it
works, but like I said it doesn't seems to work in sql server 2000. Just to be sure and make it
more easy your work this is what i have.
I Have a table like this
ID, Family, Freq
1 f1 23
1 f2 4
1 f20 2
2 f3 7
2 f5 4
2 f2 3
2 f7 3
2 f8 3
.... and so on
and I want the same as the first question, ie:
ID Famliy1 Freq Family2 Freq Family3 Freq Family4 Freq...
1 f1 23 f2 4 f20 2 - -
2 f3 7 f5 4 f2 3 f7 3 ...
And I try to use your code with a simple modifications in the columns names, but it didn't let me
use decode
I apreciate your answer
Followup January 24, 2005 - 11am Central time zone:
oh my gosh.
laughing totally out loud.
I would imagine many things you see on this site "won't actually work in sql server which is as
different from Oracle as dirt is from orange juice"
guess you'll have to ask a sql server programmer "so, what is the functional equivalent of decode
in sql server"
perhaps they have the ANSI case statement like we do as well.
decode( x, y, z )
would be
(case when x = y then z end)
Brilliant!
January 24, 2005 - 2pm Central time zone
Reviewer: VA from New Jersey, USA
I am confused :)
January 24, 2005 - 10pm Central time zone
Reviewer: A reader
Tom can you also handle SQL-SERVER disability questions ?
May be MS SQL server can use Oracle-wrapper behind the scenes then the soln will definitely work.
Just kidding. Really funny, SQL SERVER guys asking for Oracle solutions to SQL SERVER problems.
I am confused :)
So for your soln to work :
1)Crucial step :Migrate DB from SQL-SERVER TO ORACLE
2)Run Tom's Query .
May be thats what he had in mind.See people can go to great lengths to solve problems may be thats
the best thing to do here :)
January 26, 2005 - 2pm Central time zone
Reviewer: Fei
I'm not a SQL SERVER guy!!!
In fact I really hate it... but I've to do this.. and googling I found this answer and I made the
question, so sorry I didn't see the name of the URL.
Well thx anyway. and you are right CASE it's the equivalent
Not a pivot
March 4, 2005 - 12pm Central time zone
Reviewer: bakunian
Tom: I was trying to come up with a query that returns result set as.
GRANTEE GRANTEE_ROLE
DBA SELECT_CATALOG_ROLE, DELETE_CATALOG_ROLE, ....
I was asked to create a query where all of the roles granted to a user would be comma delimited and
on the same line with user's name.
I was trying pivot and other kinds of queries but without success.
We have application table that has only three application specific roles. However structure very
similar to DBA_ROLE_PRIVS. Any suggestions are greatly appreciated.
Followup March 4, 2005 - 1pm Central time zone:
stragg
search for it on this site.
It is a pivot after all
March 4, 2005 - 1pm Central time zone
Reviewer: bakunian
Sorry Tom: Your site helped me figure it out.
select grantee, max(decode(granted_role, 'SELECT_CATALOG_ROLE', granted_role, null)) ||', '||
max(decode(granted_role, 'DELETE_CATALOG_ROLE', granted_role, null)) ||', '||
max(decode(granted_role, 'EXECUTE_CATALOG_ROLE', granted_role, null))
from dba_role_privs
group by grantee
/
Merging two rows into one
March 4, 2005 - 4pm Central time zone
Reviewer: A reader from Alabama,USA
Tom,
I have a result set obtained by joining two tables. Here are the results (by joining Emp and
Dept tables, Emp has an extra column called status_cd)
Deptno Empno Status_cd
10 1234 5
10 1234 6
20 3456 5
30 1345 5
Is it possible to get something like this from this result:
Deptno Empno Status desc
10 1234 in both statuses
20 3456 only in 5
30 1345 only in 5
I am not sure if this question belongs to this thread, but i couldn't find related thread for this
one.
Thanks for your help,
Followup March 4, 2005 - 6pm Central time zone:
take the query you have already and (pretend it is "Q")
ops$tkyte@ORA9IR2> select deptno, empno,
2 case when count(*) = 1
3 then 'In status ' || max(status_cd)
4 else 'In ' || count(*) || ' statuses'
5 end status
6 from ("Q") --- "q" is your current query...
7 group by deptno, empno
8 /
DEPTNO EMPNO STATUS
---------- ---------- ----------------------------------------------------
10 1234 In 2 statuses
20 1345 In status 5
20 3456 In status 5
Merging two rows into one
March 4, 2005 - 11pm Central time zone
Reviewer: A reader from Alabama,USA
Thanks Tom, That's exactly what i was looking for. That solution is really interesting (never seen
that before).
Thanks a bunch.
Rows to columns
April 12, 2005 - 10pm Central time zone
Reviewer: A reader
create table c
(
c_pk int number primary key,
c_data varchar2(10)
);
insert into c values (1,'one');
insert into c values (2,'two');
insert into c values (3,'three');
insert into c values (4,'four');
insert into c values (5,'five');
create table e
(
e_pk int number primary key,
e_data varchar2(10)
);
insert into e values (1,'e_one');
insert into e values (2,'e_two');
insert into e values (3,'e_three');
insert into e values (4,'e_four);
create table c_e
(
c_pk int number references c,
e_pk int references e,
c_e_data varchar2(10),
constraint c1 primary key (c_pk,e_pk)
);
insert into c_e values (1,1,'one_one');
insert into c_e values (1,2,'one_two');
insert into c_e values (2,1,'two_one');
2 tables and a many-many resolver table.
Given a input of c_pk in (1,2,3,4) and e_pk in (1,2,3), I want the following output
1 one_one one_two null
2 two_one null null
3 null null null
4 null null null
The first column above represents the c_pks provided. The other 3 columns represents data
corresponding to the 3 e_pk's provided. If only e_pk in (1,2) were provided, the output should have
1+2=3 columns. Hope I have explained this clearly.
Help? Thanks
Followup April 13, 2005 - 9am Central time zone:
what if e_pk was in (1,3,4) instead.
April 12, 2005 - 11pm Central time zone
Reviewer: A reader
SQL> SELECT
2 oj.c_pk,
3 max(decode(oj.e_pk,1,c_e.c_e_data)) c1,
4 max(decode(oj.e_pk,2,c_e.c_e_data)) c2,
5 max(decode(oj.e_pk,3,c_e.c_e_data)) c3
6 FROM c_e,(SELECT c_pk,e_pk FROM c,e
7 WHERE c.c_pk IN (1,2,3,4)
8 AND e.e_pk IN (1,2,3)) oj
9 WHERE 1=1
10 AND oj.c_pk=c_e.c_pk(+)
11 AND oj.e_pk=c_e.e_pk(+)
12 GROUP BY oj.c_pk
13 /
C_PK C1 C2 C3
---------- ---------- ---------- ----------
1 one_one one_two
2 two_one
3
4
seems to do it. Is this how you would do it?
Thanks
April 13, 2005 - 9am Central time zone
Reviewer: A reader
"what if e_pk was in (1,3,4) instead"
Well, the output would still contain 1+3=4 columns and it would look like
1 one_one null null
2 two_one null null
3 null null null
4 null null null
Basically, the e_pk's determine which rows from e (and c_e of course) to show in the output
Followup April 13, 2005 - 9am Central time zone:
what if it was 1,2,3,4 then (make the problem generic, is it always "3x4" or "nxm"
3xm -- OK
nxm -- you need to write a dynamic query for each value of N
April 13, 2005 - 9am Central time zone
Reviewer: A reader
Any number of c_pk's are provided and upto 3 (1 or 2 or 3) e_pk's are provided.
Output will be NxM (where N is number of e_pk's provided and M is 1 or 2 or 3)
April 13, 2005 - 9am Central time zone
Reviewer: A reader
Output will be NxM (where N is number of e_pk's provided and M is 1 or 2 or 3)
I meant c_pk's above
Followup April 13, 2005 - 9am Central time zone:
then M will always be 3, else you need 3 queries.
ops$tkyte@ORA9IR2> variable bv1 number
ops$tkyte@ORA9IR2> variable bv2 number
ops$tkyte@ORA9IR2> variable bv3 number
ops$tkyte@ORA9IR2> variable c varchar2(20)
ops$tkyte@ORA9IR2> exec :bv1 := 1; :bv2 := 2; :bv3 := 3; :c := '1,2,3,4';
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select c_pk,
2 max( decode( e_pk, :bv1, c_e_data ) ) c1,
3 max( decode( e_pk, :bv2, c_e_data ) ) c2,
4 max( decode( e_pk, :bv3, c_e_data ) ) c3
5 from (select c.c_pk, c_e.e_pk, c_e.c_e_data
6 from c left join c_e on ( c.c_pk = c_e.c_pk )
7 )
8 where c_pk in ( select* from TABLE(cast( str2tbl(:c) as str2tblType) ) )
9 group by c_pk
10 /
C_PK C1 C2 C3
---------- ---------- ---------- ----------
1 one_one one_two
2 two_one
3
4
ops$tkyte@ORA9IR2> select c_pk,
2 (select c_e_data from c_e where c_pk = c.c_pk and e_pk = :bv1) c1,
3 (select c_e_data from c_e where c_pk = c.c_pk and e_pk = :bv2) c2,
4 (select c_e_data from c_e where c_pk = c.c_pk and e_pk = :bv3) c3
5 from c
6 where c_pk in ( select* from TABLE(cast( str2tbl(:c) as str2tblType) ) )
7 /
C_PK C1 C2 C3
---------- ---------- ---------- ----------
1 one_one one_two
2 two_one
3
4
see the query plans with temporary tables article
http://www.oracle.com/technology/oramag/oracle/04-sep/o54asktom.html for a hint on a hint that might also be useful (cardinality) and search this site for str2tbl to
see that.
HTML DB
April 16, 2005 - 12pm Central time zone
Reviewer: A reader
Can you please take a look at
http://forums.oracle.com/forums/thread.jsp?forum=137&thread=299376
I think its more of a SQL question than a HTML DB question so I was wondering if you had any ideas
Thanks
Followup April 16, 2005 - 2pm Central time zone:
doesn't look like a sql problem to me.
Can I make a row always appear? sure, easy.
Can htmldb "know" to insert a row versus try to update a row that was just made up? Doubt it (that
would be some procedural logic there...)
HTML DB
April 16, 2005 - 7pm Central time zone
Reviewer: A reader
Hm, I agree. I hope someone on the HTML DB forum has an idea. But you know HTML DB quite well, can
you think of a way to do this? Thanks
Followup April 16, 2005 - 7pm Central time zone:
like I said, you would have to program it. you would have your own custom "do the update" routine
that would have to try to update the record and if not found insert it, or look at some attribute
of the record and know "this is to be inserted"
SQL%ROWCOUNT
April 21, 2005 - 3pm Central time zone
Reviewer: A reader
Quick followup:
create table t
(
pk int primary key,
flag varchar2(1) check (flag in ('Y','N'))
);
I have a list of pks and flags. I want to update the flag to Y only if it isnt already Y. But if
the record is not there itself, I want to insert it.
update t set flag='Y'
where pk=p_pk and flag!='Y';
if (sql%rowcount=0) then
insert into t values (p_pk,'Y');
end if;
The update can return sql%rowcount=0 either because
a) the record doesnt exist or
b) the flag is already Y
If it is a), the insert is fine. But if it is b), the insert would fail with a PK violation.
How would you handle this?
Thanks
Followup April 22, 2005 - 10am Central time zone:
remove the flag!=y ?
April 22, 2005 - 11am Central time zone
Reviewer: A reader
But I want to update the flag to Y only if it isnt already Y.
Followup April 22, 2005 - 12pm Central time zone:
your choice then. path of least resistance = update it.
either:
a)
look for the record
if exists update
else insert
b)
update
if rowcount = 0
then
insert
exception to ignore dup_val error
end if
c)
just update it (it is a single character after all, no big deal)
if rowcount = 0
then
insert
end if
Rows to columns when values are text
April 22, 2005 - 2pm Central time zone
Reviewer: Jairo Ojeda from Costa Rica
Hi Tom, I need your help to write a query like these,
DROP TABLE T1;
CREATE TABLE T1
( CLIENT NUMBER(5), IDPHONE NUMBER(1), PHONE VARCHAR2(10) );
INSERT INTO T1(CLIENT, IDPHONE, PHONE) VALUES('20588', '1', '249-3501');
INSERT INTO T1(CLIENT, IDPHONE, PHONE) VALUES('20588', '2', '296-5301');
INSERT INTO T1(CLIENT, IDPHONE, PHONE) VALUES('20588', '3', '382-4411');
INSERT INTO T1(CLIENT, IDPHONE, PHONE) VALUES('20588', '3', '384-6371');
INSERT INTO T1(CLIENT, IDPHONE, PHONE) VALUES('20613', '1', '551-43-97');
INSERT INTO T1(CLIENT, IDPHONE, PHONE) VALUES('20613', '4', '233-33-33');
COMMIT;
SELECT client,
DECODE(idphone, 1, phone) home,
DECODE(idphone, 2, phone) office,
DECODE(idphone, 3, phone) selfphone,
DECODE(idphone, 4, phone) fax
FROM t1;
but I don't know how to hide the nulls, It will looks like,
SELECT client,
SUM(DECODE(idphone, 1, REPLACE(phone, '-'))) home,
SUM(DECODE(idphone, 2, REPLACE(phone, '-'))) office,
SUM(DECODE(idphone, 3, REPLACE(phone, '-'))) selfphone,
SUM(DECODE(idphone, 4, REPLACE(phone, '-'))) fax
FROM t1
GROUP BY client;
Followup April 22, 2005 - 3pm Central time zone:
max, not sum
with all rows
April 22, 2005 - 3pm Central time zone
Reviewer: Jairo Ojeda from Costa Rica
But I need TO see ALL phone VALUES, I need an output LIKE,
CLIENT HOME OFFICE SELFPHONE FAX
20588 249-3501 296-5301 384-6371
20588 382-4411
20613 551-43-97 233-33-33
Followup April 22, 2005 - 4pm Central time zone:
ops$tkyte@ORA9IR2> select client, rn,
2 max(decode(idphone,1,phone)) home,
3 max(decode(idphone,2,phone)) office,
4 max(decode(idphone,3,phone)) selfphone,
5 max(decode(idphone,4,phone)) fax
6 from (
7 select client, phone, idphone, row_number() over (partition by client, idphone order by phone)
rn
8 from t1
9 )
10 group by client, rn
11 /
CLIENT RN HOME OFFICE SELFPHONE FAX
---------- ---------- ---------- ---------- ---------- ----------
20588 1 249-3501 296-5301 382-4411
20588 2 384-6371
20613 1 551-43-97 233-33-33
analytics rock, they roll, they are the coolest thing to happen to SQL since the keyword SELECT
Cool !!!
April 22, 2005 - 5pm Central time zone
Reviewer: Jairo Ojeda from Costa Rica
Thanks a lot, I had read about "analytics functions" but I don't understand its and I never thought
that they are my solutions today,
Thanks Tom,
can not do using this....(PIVOT)
May 19, 2005 - 9am Central time zone
Reviewer: Peru from India
Hi Tom,
We are working on ACCESS to ORACLE conversion. There is query which need to be converted in ORACLE.
Though this is big, I want to give many inserts.
------ DATA ----
CREATE TABLE STAFF (
STAFF_ID NUMBER (11),
STAFFLASTNAME VARCHAR2 (4000),
STAFFFIRSTNAME VARCHAR2 (4000),
STAFFDEPT_FK VARCHAR2 (4000),
STAFFTITLE VARCHAR2 (4000),
STAFFUSERID VARCHAR2 (4000))
/
INSERT INTO STAFF ( STAFF_ID, STAFFLASTNAME, STAFFFIRSTNAME, STAFFDEPT_FK, STAFFTITLE,
STAFFUSERID ) VALUES (
2110, 'Meskin, Ilana', 'Ilana', '4103', 'Sr Dir Human Resources', 'imeskin');
INSERT INTO STAFF ( STAFF_ID, STAFFLASTNAME, STAFFFIRSTNAME, STAFFDEPT_FK, STAFFTITLE,
STAFFUSERID ) VALUES (
2112, 'Romero, Leo', 'Leo', '4910', 'Validation Eng I', 'lromero');
INSERT INTO STAFF ( STAFF_ID, STAFFLASTNAME, STAFFFIRSTNAME, STAFFDEPT_FK, STAFFTITLE,
STAFFUSERID ) VALUES (
2113, 'Marian, Mihaela', 'Mihaela', '4640', 'Assoc Mgr Opns Reg Compl', 'mmarian');
INSERT INTO STAFF ( STAFF_ID, STAFFLASTNAME, STAFFFIRSTNAME, STAFFDEPT_FK, STAFFTITLE,
STAFFUSERID ) VALUES (
2116, 'Baclaski, Jeff', 'Jeffrey', '3732', 'Principal Engineer', 'jbaclask');
INSERT INTO STAFF ( STAFF_ID, STAFFLASTNAME, STAFFFIRSTNAME, STAFFDEPT_FK, STAFFTITLE,
STAFFUSERID ) VALUES (
2117, 'Miller, Janis', 'Janis', '3760', 'Scientist', 'jfuller');
INSERT INTO STAFF ( STAFF_ID, STAFFLASTNAME, STAFFFIRSTNAME, STAFFDEPT_FK, STAFFTITLE,
STAFFUSERID ) VALUES (
2144, 'Yoshimoto, Marlo', 'Marlo', '3730', 'Sr Associate Scientist', 'marloy');
INSERT INTO STAFF ( STAFF_ID, STAFFLASTNAME, STAFFFIRSTNAME, STAFFDEPT_FK, STAFFTITLE,
STAFFUSERID ) VALUES (
2153, 'Montes, Kiko', 'Cuitlahuatl', '3732', 'Supv II Pilot Plant', 'kmontes');
INSERT INTO STAFF ( STAFF_ID, STAFFLASTNAME, STAFFFIRSTNAME, STAFFDEPT_FK, STAFFTITLE,
STAFFUSERID ) VALUES (
2154, 'Mead, Carol', 'Carol', '4830', 'QAL Associate III', 'cmead');
INSERT INTO STAFF ( STAFF_ID, STAFFLASTNAME, STAFFFIRSTNAME, STAFFDEPT_FK, STAFFTITLE,
STAFFUSERID ) VALUES (
2159, 'Porras, Michael', 'Michael', '4632', 'Constr Engineer III', 'mporras');
INSERT INTO STAFF ( STAFF_ID, STAFFLASTNAME, STAFFFIRSTNAME, STAFFDEPT_FK, STAFFTITLE,
STAFFUSERID ) VALUES (
2168, 'Ma, Yu-Heng', 'Yu-Heng', '3760', 'Scientist', 'yma');
INSERT INTO STAFF ( STAFF_ID, STAFFLASTNAME, STAFFFIRSTNAME, STAFFDEPT_FK, STAFFTITLE,
STAFFUSERID ) VALUES (
2170, 'Ysais, Robert', 'Robert', '3304', 'Assoc Mgr Mfg', 'rysais');
INSERT INTO STAFF ( STAFF_ID, STAFFLASTNAME, STAFFFIRSTNAME, STAFFDEPT_FK, STAFFTITLE,
STAFFUSERID ) VALUES (
2171, 'Santelli, Jayne', 'Jayne', '4823', 'Mgr Corp Quality Systems', 'jaynes');
INSERT INTO STAFF ( STAFF_ID, STAFFLASTNAME, STAFFFIRSTNAME, STAFFDEPT_FK, STAFFTITLE,
STAFFUSERID ) VALUES (
2172, 'Meng, Tina', 'Shi-Yuan', '3720', 'Sr Principal Scientist', 'tmeng');
INSERT INTO STAFF ( STAFF_ID, STAFFLASTNAME, STAFFFIRSTNAME, STAFFDEPT_FK, STAFFTITLE,
STAFFUSERID ) VALUES (
2173, 'Pantess, Vincent', 'Vincent', '3304', 'Mfg Specialist II', 'vpantess');
CREATE TABLE STAFFROLE (
SR_ID NUMBER (11) NOT NULL,
SR_SPONSORCODE VARCHAR2 (4000),
SR_DESC VARCHAR2 (4000))
/
INSERT INTO STAFFROLE ( SR_ID, SR_SPONSORCODE, SR_DESC ) VALUES (
-2010208894, 'CV', 'Validation Lead');
INSERT INTO STAFFROLE ( SR_ID, SR_SPONSORCODE, SR_DESC ) VALUES (
-832089475, 'BP', 'Business Process Owner');
INSERT INTO STAFFROLE ( SR_ID, SR_SPONSORCODE, SR_DESC ) VALUES (
-488754689, 'VL', 'Validation Lead');
INSERT INTO STAFFROLE ( SR_ID, SR_SPONSORCODE, SR_DESC ) VALUES (
-367269764, 'na', 'Team Member');
INSERT INTO STAFFROLE ( SR_ID, SR_SPONSORCODE, SR_DESC ) VALUES (
1, 'TL', 'Team Lead');
INSERT INTO STAFFROLE ( SR_ID, SR_SPONSORCODE, SR_DESC ) VALUES (
2, 'CL', 'Client');
INSERT INTO STAFFROLE ( SR_ID, SR_SPONSORCODE, SR_DESC ) VALUES (
3, 'CH', 'Champion');
INSERT INTO STAFFROLE ( SR_ID, SR_SPONSORCODE, SR_DESC ) VALUES (
4, 'BB', 'Black Belt');
INSERT INTO STAFFROLE ( SR_ID, SR_SPONSORCODE, SR_DESC ) VALUES (
7, 'SME', 'Subject Matter Expert');
INSERT INTO STAFFROLE ( SR_ID, SR_SPONSORCODE, SR_DESC ) VALUES (
8, 'GB', 'Green Belt');
INSERT INTO STAFFROLE ( SR_ID, SR_SPONSORCODE, SR_DESC ) VALUES (
9, 'EC', 'Executive Champion');
INSERT INTO STAFFROLE ( SR_ID, SR_SPONSORCODE, SR_DESC ) VALUES (
10, 'STH', 'Stakeholder');
INSERT INTO STAFFROLE ( SR_ID, SR_SPONSORCODE, SR_DESC ) VALUES (
13, 'SC', 'Steering Committee');
INSERT INTO STAFFROLE ( SR_ID, SR_SPONSORCODE, SR_DESC ) VALUES (
176541178, 'TR', 'Training Lead');
INSERT INTO STAFFROLE ( SR_ID, SR_SPONSORCODE, SR_DESC ) VALUES (
1090007649, 'PM', 'Project Manager');
INSERT INTO STAFFROLE ( SR_ID, SR_SPONSORCODE, SR_DESC ) VALUES (
1545110821, 'FL', 'Financial Lead');
INSERT INTO STAFFROLE ( SR_ID, SR_SPONSORCODE, SR_DESC ) VALUES (
1636171103, 'QS', 'Quality System Owner');
INSERT INTO STAFFROLE ( SR_ID, SR_SPONSORCODE, SR_DESC ) VALUES (
2030030930, 'IS', 'IS Lead');
CREATE TABLE PROJECTRESOURCE (
PR_ID NUMBER (11) NOT NULL,
PR_PS_FK NUMBER (11),
PR_STAFF_FK NUMBER (11),
PR_TBD_FLAG NUMBER (1),
PR_ROLE_FK NUMBER (11),
PR_DEPT_FK VARCHAR2 (4000),
PR_RESOURCETYPE_FK NUMBER (11),
PR_SPONSORFLAG NUMBER (1),
PR_CHANGEFLAG VARCHAR2 (4000))
/
INSERT INTO PROJECTRESOURCE ( PR_ID, PR_PS_FK, PR_STAFF_FK, PR_TBD_FLAG, PR_ROLE_FK, PR_DEPT_FK,
PR_RESOURCETYPE_FK, PR_SPONSORFLAG, PR_CHANGEFLAG ) VALUES (
-2083323726, 1112142177, 2144, 0, 7, '3730', -1571072680, 0, NULL);
INSERT INTO PROJECTRESOURCE ( PR_ID, PR_PS_FK, PR_STAFF_FK, PR_TBD_FLAG, PR_ROLE_FK, PR_DEPT_FK,
PR_RESOURCETYPE_FK, PR_SPONSORFLAG, PR_CHANGEFLAG ) VALUES (
-1595702725, 1112142177, 2116, 0, 7, '3730', -1571072680, 0, NULL);
INSERT INTO PROJECTRESOURCE ( PR_ID, PR_PS_FK, PR_STAFF_FK, PR_TBD_FLAG, PR_ROLE_FK, PR_DEPT_FK,
PR_RESOURCETYPE_FK, PR_SPONSORFLAG, PR_CHANGEFLAG ) VALUES (
-248050869, 1112142177, 2116, 0, 7, '3732', -1571072680, 0, NULL);
INSERT INTO PROJECTRESOURCE ( PR_ID, PR_PS_FK, PR_STAFF_FK, PR_TBD_FLAG, PR_ROLE_FK, PR_DEPT_FK,
PR_RESOURCETYPE_FK, PR_SPONSORFLAG, PR_CHANGEFLAG ) VALUES (
-24777811, 1112202605, 2173, 0, 7, '3304', -764152196, 0, 'P');
INSERT INTO PROJECTRESOURCE ( PR_ID, PR_PS_FK, PR_STAFF_FK, PR_TBD_FLAG, PR_ROLE_FK, PR_DEPT_FK,
PR_RESOURCETYPE_FK, PR_SPONSORFLAG, PR_CHANGEFLAG ) VALUES (
853550083, 1106778468, 2116, 0, 7, '3730', 93489626, 0, 'P');
------ACCESS QUERY--------
TRANSFORM Min(Staff.StaffLastName) AS MinOfStaffLastName
SELECT ProjectResource.PR_PS_FK
FROM StaffRole INNER JOIN (Staff INNER JOIN ProjectResource ON Staff.Staff_Id =
ProjectResource.PR_Staff_FK) ON StaffRole.SR_Id = ProjectResource.PR_Role_FK
WHERE (((StaffRole.SR_SponsorCode)<>"na"))
GROUP BY ProjectResource.PR_PS_FK
PIVOT StaffRole.SR_SponsorCode In ("EC","CH","PM","TL","BB","GB","TR","IS");
OUT PUT format
PR_PS_FK EC CH PM TL BB GB TR IS
-------- -- -- ---- --- --- --- --- ---
Kindly help us to achive this. I have tried you mathod. but can not do .
Followup May 19, 2005 - 10am Central time zone:
how about an example of the output you expect from this?
for you see, i've not any idea what "transform" means.
One more solution
May 19, 2005 - 12pm Central time zone
Reviewer: A reader from USA
One more solution
http://www.oracle.com/technology/oramag/code/tips2004/050304.html
Followup May 19, 2005 - 1pm Central time zone:
Ouch -- not a bind variable in place. Ouch Ouch Ouch.
Avoid that approach please.
stragg
search for that on this site.
Good
June 16, 2006 - 2pm Central time zone
Reviewer: Mahmood Lebbai. from Bothell,WA USA
The examples given could have been more simpler for a quicker understanding.Most of the people come
up with very big questions. It would be easier if the questions asked are precise,specific and
to-the-point,instead of beating around the bush.It would be of great help in undestanding for a
budding oracle DBAs or developers.
Followup June 16, 2006 - 7pm Central time zone:
the examples or the questions.
You see, I control the examples - YOU guys control the questions :)
In a single query
August 24, 2006 - 9am Central time zone
Reviewer: Anvar from India
Tom,
How can we get the rows into columns using a single query?
For e.g., the requirement is to have the following output.
PRESIDENT MANAGER ANALYST CLERK SALESMAN
------------------------------------------------
KING BLAKE FORD ADAMS ALLEN
CLARK SCOTT JAMES MARTIN
JONES MILLER TURNER
SMITH WARD
Followup August 27, 2006 - 7pm Central time zone:
scott%ORA10GR2> select rn,
2 max( decode( job, 'PRESIDENT', ename )) pres,
3 max( decode( job, 'MANAGER', ename )) mgr,
4 max( decode( job, 'ANALYST', ename )) analyst,
5 max( decode( job, 'CLERK', ename )) clerk,
6 max( decode( job, 'SALESMAN', ename )) sales
7 from (
8 select ename,
9 job,
10 row_number() over (partition by job order by ename) rn
11 from emp
12 )
13 group by rn
14 order by rn
15 /
RN PRES MGR ANALYST CLERK SALES
---------- ---------- ---------- ---------- ---------- ----------
1 KING BLAKE FORD ADAMS ALLEN
2 CLARK SCOTT JAMES MARTIN
3 JONES MILLER TURNER
4 SMITH WARD
tbl2str, for. arbitrary number of rows?
September 21, 2006 - 12pm Central time zone
Reviewer: Duke Ganote from Terra\USA\Ohio\ClertmontCounty\BataviaTownship
Just for practice (and to handle an arbitary number of columns), I reversed STR2TBL into TBL2STR:
create or replace type myTableType
as table of number
/
create or replace
function tbl2str( p_in IN myTableType )
return varchar2
as
l_str VARCHAR2(4000);
l_n number;
begin
FOR l_n IN 1..p_in.count loop
l_str := l_str
|| CASE WHEN l_str is not null
THEN ',' END
|| p_in( l_n );
end loop;
return l_str;
end;
/
select tbl2str(
cast(
multiset( select level as lvl
from dual
connect by level < 10
) as myTableType
)
) "one long string"
from dual
/
one long string
------------------
1,2,3,4,5,6,7,8,9
TBL2STR ~~ STRAGG
October 2, 2006 - 4pm Central time zone
Reviewer: Duke Ganote from Terra\USA\Ohio\ClermontCounty\BataviaTownship
Tom-- Looks like you already proposed another approach to "string aggregation"
http://asktom.oracle.com/pls/ask/f?p=4950:8:7327261616818619917::NO::F4950_P8_DISPLAYID,F4950_P8_CRI
TERIA:2196162600402 I learn lots while trawling your site!
I lament there is no index to easily find the original STRAGG question and response; there are lots
of questions referencing STRAGG now.
How do you fetch the refcursor if you don't know how many colums you have?
October 5, 2006 - 7pm Central time zone
Reviewer: Michael Hanson from Wood Dale, IL(suburb of Chicago) USA
Near the begining of the thread you gave a perfect example of how I need to display rows as
columns. The only problem is how do you fetch the refcursor into something when you don't know
exactly how many columns you are going to return. In your example you printed the contents of the
ref cursor by doing the following:
"variable x refcursor
set autoprint on
exec pivot.data( :x );"
Thats great for displaying in sql plus, but what about a stored procedure? I need to fetch the ref
cursor in a procedure and do a lot more processsing. I did read another one of your threads and
you said if you don't know how many columns your returning, you can't use a ref cursor. You
suggested to use DMBS_SQL. I looked that up as well and its still not clear how to fetch the data
into something when you don't know how many columns your returning. Is there any way to make your
rows into columns example work within a procedure? Can you give me an example?
Followup October 6, 2006 - 8am Central time zone:
if you need to procedurally process a result set in plsql, you use dbms_sql. All other 3gls have
the ability to retrieve a ref cursor and "describe it" (so the program can figure out "hey, I got
15 columns this time"
So, if you need to do this in plqsl, you'll need to rewrite it using dbms_sql and use dbms_sql
instead. there you can ask "how many columns" and get the "i'th column"
The solutions helped me a lot.
November 28, 2006 - 5am Central time zone
Reviewer: vijay from India
The ideas, tricks given by Tom really helps a lot. Thanks very much !
Also: look out for the resource consumption.....
November 29, 2006 - 8am Central time zone
Reviewer: Jens from Norway
We had a similar rows-to-columns problem, with a fixed number of columns (actually not fixed, just
maximum 7), and came up with the 2 following solutions (in that order):
set timing on;
set autotrace traceonly statistics;
--/*
select v1.td_info_id, v1.value val1, v2.value val2, v3.value val3, v4.value val4, v5.value val5,
v6.value val6, v7.value val7
from labdb_td_results v1
left join labdb_td_results v2 on ((nvl(v1.result_count,-1)+1) = v2.result_count and v1.td_info_id =
v2.td_info_id and v2.field_id=2)
left join labdb_td_results v3 on ((nvl(v1.result_count,-1)+2) = v3.result_count and v1.td_info_id =
v3.td_info_id and v3.field_id=3)
left join labdb_td_results v4 on ((nvl(v1.result_count,-1)+3) = v4.result_count and v1.td_info_id =
v4.td_info_id and v4.field_id=4)
left join labdb_td_results v5 on ((nvl(v1.result_count,-1)+4) = v5.result_count and v1.td_info_id =
v5.td_info_id and v5.field_id=5)
left join labdb_td_results v6 on ((nvl(v1.result_count,-1)+5) = v6.result_count and v1.td_info_id =
v6.td_info_id and v6.field_id=6)
left join labdb_td_results v7 on ((nvl(v1.result_count,-1)+6) = v7.result_count and v1.td_info_id =
v7.td_info_id and v7.field_id=7)
where v1.field_id = 1
and v1.td_info_id=235;
892 rows selected.
Elapsed: 00:00:00.42
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
519 consistent gets
55 physical reads
0 redo size
23409 bytes sent via SQL*Net to client
647 bytes received via SQL*Net from client
61 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
892 rows processed
versus the analytics:
set timing on;
set autotrace traceonly statistics;
select td_info_id, rn
, max(decode(field_id, 1, value)) val1
, max(decode(field_id, 2, value)) val2
, max(decode(field_id, 3, value)) val3
, max(decode(field_id, 4, value)) val4
, max(decode(field_id, 5, value)) val5
, max(decode(field_id, 6, value)) val6
, max(decode(field_id, 7, value)) val7
from (
select td_info_id, field_id, value, row_number() over (partition by td_info_id, field_id order by
result_id) rn
from labdb_td_results
where td_info_id=235
)
group by td_info_id, rn
order by td_info_id, rn;
892 rows selected.
Elapsed: 00:00:00.15
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
57 consistent gets
0 physical reads
0 redo size
24170 bytes sent via SQL*Net to client
647 bytes received via SQL*Net from client
61 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
892 rows processed
So half the elapsed time, 10% of the consistent gets in the analytics case. This is in version
10.2.0.1.0.
I haven't bothered to post the entire test-case (and yes, we do use bind-variables in the
application query..), my point just being:
Make sure to measure performance and resource consumption of the various alternatives.
Jens
March 4, 2007 - 11pm Central time zone
Reviewer: A reader
Hi Tom,
Table having huge volume of data and want to split single row to multiple rows.I mean split based on acc1 and id1
ex:
SELECT acc1,id1,acc2, id2 FROM TGDW_ACCT
acc1 id1 acc2 id2
6000161114 002 6000251684 001
6000161115 001 6000251687 004
Expecting o/p
------------
acc1 id1
6000161114 002
6000251684 001
6000161115 001
6000251687 004
Thanks and Regards,
MR
Followup March 5, 2007 - 12pm Central time zone:
with data
as
(select 1 r from dual union all select 2 r from dual)
select decode( r, 1, id1, 2, id2 ) id,
decode( r, 1, acc1, 2, acc2 ) acc
from tgdw_acct, data
/
If new rows are added every now and then
June 28, 2007 - 10am Central time zone
Reviewer: A reader
I have this table
Class Teacher
A Margaritta Gonson
A Eric Billy
A Faruq Omar
B Antouan Schensez
B Michael Gabriella
C Haluk Gumuskaya
D Gokhan Yavuz
D Banu Diri
i get the below result after running this
SELECT c.class_name,
MAX(decode(r, 1, c.teacher_name, NULL)) TEACHER_1,
MAX(decode(r, 2, c.teacher_name, NULL)) TEACHER_2,
MAX(decode(r, 3, c.teacher_name, NULL)) TEACHER_3
FROM
(SELECT row_number() over(PARTITION BY class_name ORDER BY teacher_name) r,class_name,teacher_name FROM classes) c
GROUP BY c.class_name
ORDER BY c.class_name;
Class Teacher 1 Teacher2 Teacher 3
A Eric Billy Faruq Omar Margaritta Gonson
B Antouan Schensez Michael Gabriella
C Haluk Gumuskaya
D Banu Diri Gokhan Yavuz
If i want this for N number of teachers then how do i do it.
that mean the number of teahcer can increase and decrease. how do i handle that case then for once and for all.
I need a dynamic code for this with out using PLSQL codings/fuctions.
Thanks for your help
Followup July 2, 2007 - 10am Central time zone:
you need to know the number of columns a query will return - when you parse the query.
think about it....
if you can deal with varrays/collections - we can return an "array", but not an unknown number of columns.
index-by-table rows to columns
June 23, 2008 - 2am Central time zone
Reviewer: Madhuri from USA
Tom,
Your website is such a huge help to many of us out here.
Thanks for all the help we get here.
Coming to my question , similar to converting rows to columns but what I am trying to do is :
Convert the elements of an index by table to be displayed as follows :
The index should be displayed as the column title.
The value should be displayed as the value in the row for that column.
eg:
Index Value
14227 X
14229 B
14232 Y
14236 A
The output ( which will be used in a report ) should look like :
14227 14229 14232 14236 ...
X B Y A
The number of elements is not known ahead of time. The index by table is built in a function. This
function could probably return a refcursor to my actual query which also selects 4 other columns
from a join on a few tables.
I will actually be replacing these numbers 14227 etc., to a corresponding "Code/Name" which will be
the actual column title.
Thank you so much!
Followup June 23, 2008 - 8am Central time zone:
you would have to
a) run a query to find the number of columns you'll have
b) dynamically construct such a query
that is the only way - it is not worth it. I cannot imagine how this would be useful in real life - data that stretches for miles left and right is useless by humans - we just cannot look at data that way - it is not normal.
It is useful
August 5, 2008 - 12pm Central time zone
Reviewer: A reader from Nashville, TN
It's useful to be able to convert from rows to columns, when you don't know the number of columns
that are being created, or their exact names.
I suspected that the only way to do that, currently, is to write a Query to determined the number
of columns you would have, then dynamically construct the query.
That makes it not worth it, but it would be a great idea, for the simple fact, that when you are
told to pivot the data, you must do it. It doesn't really matter exactly the names of the pivot,
nor does it matter exactly the number.
That doesn't make it infinite. Whether its 8 or 12, I don't think thats important, its just
important to have clean code that does the job. This MAX and DECODE seems like a kind of hack in
this modern world.
My two cents.
Great resource, very good to find the 'answer' even if I didn't like it, better than not knowing.
Followup August 5, 2008 - 1pm Central time zone:
... I suspected that the only way to do that, currently, is to write a Query to
determined the number of columns you would have, then dynamically construct the
query.
....
correct - and then the client needs to know what they just selected anyway.... so, they sort of need to know this.
it absolutely matters the number and datatypes of the pivoted data - the CLIENT (you are the client in this case, the thing executing the query) sort of needs to know this.
If you just want an "array of data", you can use the COLLECT aggregate - else you do care and need to know the number of columns and their types.
There is a PIVOT syntax nowadays, so yes, max/decode is "old", but you still (ANSI says so too) need to know their names and the number of them.
Need Your Help " TOM "
September 3, 2009 - 5am Central time zone
Reviewer: Keerthi C from India
Hi Tom,
I have a table like this :
Project Date Hours
-------- ----- ------
01 - Holiday 08/29/2009 08
01 - Holiday 08/28/2009 08
o2 - ABC 08/27/2009 08
02 - ABC 08/26/2009 08
02 - ABC 08/25/2009 08
I want output as Follows ..
01 - Holiday 08/29/2009 08/28/2009
------------- ---------- ---------
08 08
02 - ABC 08/27/2009 08/26/2009 08/25/2009
------------- ---------- --------- ----------
08 08 08
Hope you respond to this ASAP :)
Thanx,
Keerthi C
Followup September 3, 2009 - 7am Central time zone:
search this site for pivot
and unless you know how many columns a query will have at parse time, it won't work in sql.
meaning, I don't think SQL is going to do anything for you here, you don't know what column names you will have, you don't know how many you will have - for each ROW even.
You'll be doing this in code in whatever formats the output.
Need your help @ this also :)
September 3, 2009 - 5am Central time zone
Reviewer: Keerthi C from India
Hi Tom,
I have a table like this :
Project Date Hours
-------- ----- ------
01 - Holiday 08/29/2009 08
01 - Holiday 08/28/2009 08
o2 - ABC 08/27/2009 08
02 - ABC 08/26/2009 08
02 - ABC 08/25/2009 08
I want output as Follows ..
08/29/2009 08/28/2009 08/27/2009 08/26/2009 08/25/2009
---------- --------- ---------
01 - Holiday 08 08
02 - ABC 08 08 08
September 3, 2009 - 8am Central time zone
Reviewer: Keerthi C from India
Hi Tom,
I have a table like this ..
Project Date Hours
----- ---- -----
A 1 8
A 2 8
B 3 8
C 4 8
D 5 8
If table is queried , I need like ..
Date 1 2 3 4 5
Project A B C D E
Hours 8 8 8 8 8
Can anyone help me on this issue ?
Thanx,
Abaya
September 27, 2009 - 8am Central time zone
Reviewer: Monika from India
Hi Tom,
I have a similar issue. My issue is that i have to take row values as column name. Here i am taking
HRMS tables like per_elements_type_f table i have to select elemnt name (eg Cost To Company,HRA,LTA
etc) and these for each employee i hve to find out the values for these so my table struecture has
be like as shown below:
Employee Number Emp Name CTC HRA
1 ABC 1000000 5000
soo this CTC,HRA etc are column value from one table and these values are also column value from
othere table so can anybody suggest me that how to write a select query for this case?
Best Regards,
Monika
Followup September 30, 2009 - 7am Central time zone:
no create table
no inserts
no real example
no look
you seem to assume I work with the same applications you do :)
but if all you are asking to do is pivot some data, please re-read this page - it shows the technique.
if you have a table (assumption on my part) that looks like:
empno attribute value
------- ---------- ---------
1 name abc
1 ctc 100000
1 hra 5000
2 name def
......
In 10g and before you would
select empno,
max( decode( attribute, 'name', value ) ),
max( decode( attribute, 'ctc', value ) ),
max( decode( attribute, 'hra', value ) )
from t
group by empno
In 11g, you can use pivot instead (new sql construct)
pivot
October 2, 2009 - 9am Central time zone
Reviewer: A reader
please tune this query with similar problem
December 21, 2009 - 3pm Central time zone
Reviewer: A reader from mexico
Select /*+ FIRST_ROWS */
Rcta.Org_id Id_org, Rcus.Customer_id Customer_id, Rcus.Customer_name Nombre_cliente,
Rcus.Customer_number Nro_cliente,
Decode(Rcus.Customer_type, 'I', 'Nacional', 'Extranjero') Tipo_cliente,
Rcta.Customer_trx_id Id_documento, Rcta.Trx_number Nro_transaccion,
Rcta.Type Categoria, Rbso.Name Serie, Rcta.Name Tipo_transaccion,
Rcta.Invoice_currency_code Moneda, Rcta.Exchange_rate Rate_cambio,
Rcta.Trx_date Fecha_transac, To_date(Decode(Rcta.Type, 'CM', Null, Psch.Due_date))
Fecha_vencimiento, Rcta.Ct_reference Referencia,
Cta_rec Cta_x_cob, Cta_rev Cta_ingresos, Decode(Rcta.Type, 'BR', Null, Cta_tax) Cta_iva,
Decode(
Rcta.Type,
'BR', Null,
Cta_ret) Cta_retencion,
Xxcn_contratos_util_pkg.Get_cps(Rcta.Customer_trx_id) No_contrato,
Xxcn_contratos_util_pkg.Get_cps_type(Rcta.Customer_trx_id) Tipo_contrato,
(Select User_name
From Apps.Fnd_user
Where User_id = Rcta.Last_updated_by) Usuario
From (Select Max( Decode(
Lgd.Account_class,
'REC', Gcco.Segment1
|| '-'
|| Gcco.Segment2
|| '-'
|| Gcco.Segment3
|| '-'
|| Gcco.Segment4
|| '-'
|| Gcco.Segment5
|| '-'
|| Gcco.Segment6
|| '-'
|| Gcco.Segment7,
Null)) Cta_rec,
Max( Decode(
Lgd.Account_class,
'REV', Gcco.Segment1
|| '-'
|| Gcco.Segment2
|| '-'
|| Gcco.Segment3
|| '-'
|| Gcco.Segment4
|| '-'
|| Gcco.Segment5
|| '-'
|| Gcco.Segment6
|| '-'
|| Gcco.Segment7,
Null)) Cta_rev,
Max( Case
When(Lgd.Account_class = 'TAX' And Gcco.Segment3 In('204', '015',
'021', '036', '018')) Then Gcco.Segment1
|| '-'
|| Gcco.Segment2
|| '-'
|| Gcco.Segment3
|| '-'
|| Gcco.Segment4
|| '-'
|| Gcco.Segment5
|| '-'
|| Gcco.Segment6
|| '-'
|| Gcco.Segment7
End) Cta_tax,
Max( Case
When(Lgd.Account_class = 'TAX' And Gcco.Segment3 = '033') Then
Gcco.Segment1
|| '-'
||
Gcco.Segment2
|| '-'
||
Gcco.Segment3
|| '-'
||
Gcco.Segment4
|| '-'
||
Gcco.Segment5
|| '-'
||
Gcco.Segment6
|| '-'
||
Gcco.Segment7
End) Cta_ret,
Lgd.Customer_trx_id
From Apps.Gl_code_combinations Gcco, Apps.Ra_cust_trx_line_gl_dist Lgd
Where Lgd.Code_combination_id = Gcco.Code_combination_id(+)
Group By Lgd.Customer_trx_id) Rglg,
Apps.Ra_batch_sources_all Rbso,
Apps.Ra_customers Rcus,
Apps.Ar_payment_schedules_all Psch,
(Select /*+ INDEX( Rctt,RA_CUST_TRX_TYPES_U1) */
Rct.Customer_trx_id, Rct.Trx_date, Rct.Trx_number, Rct.Ct_reference,
Rct.Invoice_currency_code, Rct.Term_id,
Rct.Exchange_rate_type, Rct.Exchange_date, Rct.Exchange_rate,
Rct.Cust_trx_type_id, Rct.Org_id, Rct.Batch_source_id, Rctt.Type,
Rctt.Name, Rct.Bill_to_customer_id, Rct.Last_updated_by,
Decode(
Rctt.Type,
'BR', (Select Trxf.Customer_trx_id
From Apps.Ra_customer_trx_all Trxf, Apps.Ra_customer_trx_all
Rct_br, Apps.Ra_customer_trx_lines_all Rctl_br
Where Rctl_br.Customer_trx_id = Rct_br.Customer_trx_id
And Rctl_br.Br_ref_customer_trx_id = Trxf.Customer_trx_id
And Rct_br.Customer_trx_id = Rct.Customer_trx_id
And Rownum = 1),
Rct.Customer_trx_id) Customer_trx_id_cta,
Rct.Complete_flag
From Apps.Ra_customer_trx_all Rct, Apps.Ra_cust_trx_types_all Rctt
Where Rctt.Cust_trx_type_id = Rct.Cust_trx_type_id) Rcta
Where Rcta.Customer_trx_id = Psch.Customer_trx_id(+)
And Rcta.Batch_source_id = Rbso.Batch_source_id
And Rcta.Bill_to_customer_id = Rcus.Customer_id(+)
And Rcta.Customer_trx_id_cta = Rglg.Customer_trx_id(+)
And Rcta.Complete_flag = 'N'
In Above query taking more than 5 hours and "Ra_cust_trx_line_gl_dist" table has more than 22
milion records..please advise me some tuing.....
Followup December 21, 2009 - 4pm Central time zone:
hah, my favorite kind of question. (not, No one can answer these)
remove the first rows hint, it would seem you are interested in throughput, not using indexes.
If you have 10g or up and you have the tuning diagnostic packs, let it have at it and see what it says.
December 22, 2009 - 8am Central time zone
Reviewer: A reader
Respected Sir,
In our DWH environment we are bringing a new feed and it has 400 columns our data block is 8K. our
team is think to split the table horizontally into 4 tables and join it for reporting purpose
My question is
(1) if the row is too big to fit in a single block will row chaining effect the performance
(2) what your opinion whether to split table in 4 table or place all column in a single table
Please kindly advice
Many thanks
Followup December 31, 2009 - 7am Central time zone:
why do you think that. Please give us your "reasoning" behind that. I'm not saying "you are wrong", rather I'd like to hear your scientific, fact based, measured reasoning for doing something
... that is probably wrong.
That's all.
(1) we'll chain every row with more than 254 columns anyway - these rows will be stored in two row pieces regardless (you want 4??? really???). The row pieces WE store will be more efficient to put together than the 4 you store as we'll have a direct pointer from row piece to row piece - whereas you would have to use an index (3 to 5 extra IO's for each row) OR you'll have to hash one of the tables into memory and hash join to it. I'd rather have a chained row myself.
(2) probably NOT, but I'd have to hear YOUR reasoning for doing so first to evaluate - the answer, as always, is "IT DEPENDS". So, give me something to consider.
How 'bout
December 28, 2009 - 3pm Central time zone
Reviewer: Stephan Uzzell
Is there some variant pivot technique that could take a column and split it into columns based on the contents of a different column?
Maybe a test bed will be easier than the description:
SQL> create table testdata ( code varchar2(30), id number );
Table created.
SQL> insert into testdata (select object_name, mod(rownum,2) from user_objects w
here rownum<30);
29 rows created.
SQL> commit;
Commit complete.
SQL> select * from testdata;
CODE ID
------------------------------ ----------
APS_VALIDATE 1
XYZABC 0
J 1
SYS_C00228674 0
PLSQL_PROFILER_RUNS 1
PLSQL_PROFILER_UNITS 0
Now, the desired output would be something like:
ID_0 ID_1
---------------------------- ------------------------------
XYZABC APS_VALIDATE
SYS_C00228674 J
PLSQL_PROFILER_UNITS PLSQL_PROFILER_RUNS
I've tried getting this with a max( decode( )) trick, but that seems to get me the (single) max CODE for each ID...
Thanks, Tom!
Followup January 4, 2010 - 8am Central time zone:
since rows in a table have NO ORDER whatsoever, how did you know -what "logic" did you use - to put XYZABC and APS_VALIDATE together.
There is no reason the result set output would not be:
SQL> select * from testdata;
CODE ID
------------------------------ ----------
APS_VALIDATE 1
J 1
SYS_C00228674 0
PLSQL_PROFILER_RUNS 1
PLSQL_PROFILER_UNITS 0
XYZABC 0
what would your answer then be - you are missing a lot of stuff here.
December 31, 2009 - 3am Central time zone
Reviewer: Sunil from UK
Try following query:
SELECT *
FROM (SELECT LEAD (id0) OVER (ORDER BY rnum) id_0, id1 id_1
FROM ( SELECT DECODE (id, 0, code, NULL) id0,
id,
DECODE (id, 1, code, NULL) id1,
ROWNUM rnum
FROM testdata
ORDER BY id))
WHERE id_1 IS NOT NULL
Followup January 4, 2010 - 10am Central time zone:
it presumes rows have some order.
It fails - the original poster is missing crucial information - information as to how to control the order of the rows.
once we have that, using analytics will be trivial.
until then, it is NOT POSSIBLE in a predicable fashion, not at all possible.
Groups are always the same size?
December 31, 2009 - 9am Central time zone
Reviewer: Matt McPeak from Collegeville, PA
Neat! But it assumes that the groups always have the same number of rows. What do you think of this variation (below)?
SELECT id_0, id_1
FROM (SELECT code id_0,
ID,
LEAD (code) OVER (PARTITION BY rn ORDER BY ID) id_1
FROM (SELECT code,
ID,
ROW_NUMBER () OVER (PARTITION BY ID ORDER BY code)
rn
FROM TESTDATA)
ORDER BY rn, ID)
WHERE ID = 0
Followup January 4, 2010 - 10am Central time zone:
see above, they all fail - until there is a manner in which to ORDER the data.
or until the specification is changed.
Thank you Sunil and Matt!
December 31, 2009 - 2pm Central time zone
Reviewer: Stephan Uzzell
Very cool, and I thank you both! Just to prove to myself that I understood what you were doing, I worked it up to do three columns:
SELECT id_0
, id_1
, id_2
FROM (
SELECT code id_1
, ID
, LEAD( code ) OVER ( PARTITION BY rn ORDER BY ID ) id_2
, LAG( code ) OVER ( PARTITION BY rn ORDER BY ID ) id_0
FROM (
SELECT code
, ID
, ROW_NUMBER() OVER ( PARTITION BY ID ORDER BY code ) rn
FROM TESTDATA
)
ORDER BY rn
, ID
)
WHERE ID = 1
Thanks to both of you, and to Tom for hosting this great site!
|