October 5, 2011 at 12:51 am
All,
I am working on a different requirement (according to me 🙂 ) to transpose the dynamic column into rows. The details are as below.
create table pvt_test
(
txt text
)
insert into pvt_test
select '"DATES""INSTRUMENT(MNEMONIC)""""DATATYPES""""12-31-71""""06-30-11""""M""475""12-31-71""""01-31-72""""02-29-72""""03-31-72""""04-30-72""""05-31-72""""06-30-72""""07-31-72""""08-31-72""""09-30-72""""10-31-72""""11-30-72""""12-31-72""""01-31-73""""02-28-73""""03-31-73""""04-30-73""""05-31-73""""06-30-73""""07-31-73""""08-31-73""""09-30-73""""10-31-73""""11-30-73""""12-31-73""""01-31-74""""02-28-74""""03-31-74""""04-30-74""""05-31-74""""06-30-74""""07-31-74""""08-31-74""""09-30-74""""10-31-74""""11-30-74""""12-31-74""""01-31-75""""02-28-75""""03-31-75""""04-30-75""""05-31-75""""06-30-75""""07-31-75""""08-31-75""""09-30-75""""10-31-75""""11-30-75""""12-31-75""""01-31-76""""02-29-76""""03-31-76""""04-30-76""""05-31-76""""06-30-76""""07-31-76""""08-31-76""""09-30-76""""10-31-76""""11-30-76""""12-31-76""""01-31-77""""02-28-77""""03-31-77""""04-30-77""""05-31-77""""06-30-77""""07-31-77""""08-31-77""""09-30-77""""10-31-77""""11-30-77""""12-31-77""""01-31-78""""02-28-78""""03-31-78""""04-30-78""""05-31-78""""06-30-78""""07-31-78""""08-31-78""""09-30-78""""10-31-78""""11-30-78""""12-31-78""""01-31-79""""02-28-79""""03-31-79""""04-30-79""""05-31-79""""06-30-79""""07-31-79""""08-31-79""""09-30-79""""10-31-79""""11-30-79""""12-31-79""""01-31-80""""02-29-80""""03-31-80""""04-30-80""""05-31-80""""06-30-80""""07-31-80""""08-31-80""""09-30-80""""10-31-80""""11-30-80""""12-31-80""""01-31-81""""02-28-81""""03-31-81""""04-30-81""""05-31-81""""06-30-81""""07-31-81""""08-31-81""""09-30-81""""10-31-81""""11-30-81""""12-31-81""""01-31-82""""02-28-82""""03-31-82""""04-30-82""""05-31-82""""06-30-82""""07-31-82""""08-31-82""""09-30-82""""10-31-82""""11-30-82""""12-31-82""""01-31-83""""02-28-83""""03-31-83""""04-30-83""""05-31-83""""06-30-83""""07-31-83""""08-31-83""""09-30-83""""10-31-83""""11-30-83""""12-31-83""""01-31-84""""02-29-84""""03-31-84""""04-30-84""""05-31-84""""06-30-84""""07-31-84""""08-31-84""""09-30-84""""10-31-84""""11-30-84""""12-31-84""""01-31-85""""02-28-85""""03-31-85""""04-30-85""""05-31-85""""06-30-85""""07-31-85""""08-31-85""""09-30-85""""10-31-85""""11-30-85""""12-31-85""""01-31-86""""02-28-86""""03-31-86""""04-30-86""""05-31-86""""06-30-86""""07-31-86""""08-31-86""""09-30-86""""10-31-86""""11-30-86""""12-31-86""""01-31-87""""02-28-87""""03-31-87""""04-30-87""""05-31-87""""06-30-87""""07-31-87""""08-31-87""""09-30-87""""10-31-87""""11-30-87""""12-31-87""""01-31-88""""02-29-88""""03-31-88""""04-30-88""""05-31-88""""06-30-88""""07-31-88""""08-31-88""""09-30-88""""10-31-88""""11-30-88""""12-31-88""""01-31-89""""02-28-89""""03-31-89""""04-30-89""""05-31-89""""06-30-89""""07-31-89""""08-31-89""""09-30-89""""10-31-89""""11-30-89""""12-31-89""""01-31-90""""02-28-90""""03-31-90""""04-30-90""""05-31-90""""06-30-90""""07-31-90""""08-31-90""""09-30-90""""10-31-90""""11-30-90""""12-31-90""""01-31-91""""02-28-91""""03-31-91""""04-30-91""""05-31-91""""06-30-91""""07-31-91""""08-31-91""""09-30-91""""10-31-91""""11-30-91""""12-31-91""""01-31-92""""02-29-92""""03-31-92""""04-30-92""""05-31-92""""06-30-92""""07-31-92""""08-31-92""""09-30-92""""10-31-92""""11-30-92""""12-31-92""""01-31-93""""02-28-93""""03-31-93""""04-30-93""""05-31-93""""06-30-93""""07-31-93""""08-31-93""""09-30-93""""10-31-93""""11-30-93""""12-31-93""""01-31-94""""02-28-94""""03-31-94""""04-30-94""""05-31-94""""06-30-94""""07-31-94""""08-31-94""""09-30-94""""10-31-94""""11-30-94""""12-31-94""""01-31-95""""02-28-95""""03-31-95""""04-30-95""""05-31-95""""06-30-95""""07-31-95""""08-31-95""""09-30-95""""10-31-95""""11-30-95""""12-31-95""""01-31-96""""02-29-96""""03-31-96""""04-30-96""""05-31-96""""06-30-96""""07-31-96""""08-31-96""""09-30-96""""10-31-96""""11-30-96""""12-31-96""""01-31-97""""02-28-97""""03-31-97""""04-30-97""""05-31-97""""06-30-97""""07-31-97""""08-31-97""""09-30-97""""10-31-97""""11-30-97""""12-31-97""""01-31-98""""02-28-98""""03-31-98""""04-30-98""""05-31-98""""06-30-98""""07-31-98""""08-31-98""""09-30-98""""10-31-98""""11-30-98""""12-31-98""""01-31-99""""02-28-99""""03-31-99""""04-30-99""""05-31-99""""06-30-99""""07-31-99""""08-31-99""""09-30-99""""10-31-99""""11-30-99""""12-31-99""""01-31-00""""02-29-00""""03-31-00""""04-30-00""""05-31-00""""06-30-00""""07-31-00""""08-31-00""""09-30-00""""10-31-00""""11-30-00""""12-31-00""""01-31-01""""02-28-01""""03-31-01""""04-30-01""""05-31-01""""06-30-01""""07-31-01""""08-31-01""""09-30-01""""10-31-01""""11-30-01""""12-31-01""""01-31-02""""02-28-02""""03-31-02""""04-30-02""""05-31-02""""06-30-02""""07-31-02""""08-31-02""""09-30-02""""10-31-02""""11-30-02""""12-31-02""""01-31-03""""02-28-03""""03-31-03""""04-30-03""""05-31-03""""06-30-03""""07-31-03""""08-31-03""""09-30-03""""10-31-03""""11-30-03""""12-31-03""""01-31-04""""02-29-04""""03-31-04""""04-30-04""""05-31-04""""06-30-04""""07-31-04""""08-31-04""""09-30-04""""10-31-04""""11-30-04""""12-31-04""""01-31-05""""02-28-05""""03-31-05""""04-30-05""""05-31-05""""06-30-05""""07-31-05""""08-31-05""""09-30-05""""10-31-05""""11-30-05""""12-31-05""""01-31-06""""02-28-06""""03-31-06""""04-30-06""""05-31-06""""06-30-06""""07-31-06""""08-31-06""""09-30-06""""10-31-06""""11-30-06""""12-31-06""""01-31-07""""02-28-07""""03-31-07""""04-30-07""""05-31-07""""06-30-07""""07-31-07""""08-31-07""""09-30-07""""10-31-07""""11-30-07""""12-31-07""""01-31-08""""02-29-08""""03-31-08""""04-30-08""""05-31-08""""06-30-08""""07-31-08""""08-31-08""""09-30-08""""10-31-08""""11-30-08""""12-31-08""""01-31-09""""02-28-09""""03-31-09""""04-30-09""""05-31-09""""06-30-09""""07-31-09""""08-31-09""""09-30-09""""10-31-09""""11-30-09""""12-31-09""""01-31-10""""02-28-10""""03-31-10""""04-30-10""""05-31-10""""06-30-10""""07-31-10""""08-31-10""""09-30-10""""10-31-10""""11-30-10""""12-31-10""""01-31-11""""02-28-11""""03-31-11""""04-30-11""""05-31-11""""06-30-11"""'
union all
select '"696586""FAMER""""PCH#(X(RI)~U$""1M)*1.000""""12-31-71""""06-30-11""""M""475""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""10.277-22.581-7.87-19.59826.875-15.27116.279-14-4.65160.97657.576-21.635-2.45420.755-21.875-12.667-40.45843.59-14.286-43.22935.78-31.081-15.196131.214-16.96-27.746-6.667-48.2863.801-22.823-1.724-17.193-38.983-16.667-8.333-31.818-78.667-6.25173.333-75.6107088.23556.25-2032.5-18.868-30.23316.66728.57111.111-10-22.222-14.2860-4011.111750-28.571-2410.52619.048-36087.5-20-29.1675.882-5011.111-4000-41.667-37.143-54.545700-62.5-46.6676.255.882-11.111-6.25-13.333-23.07740-7.143-7.6920-45.833-7.69266.6678011.11140185.714-25-50-33.3331147.525140-41.66728.571-22.222-14.2868.333-23.077-18-2.43922.5-69.38820172.222-65.30617.64725-400-33.333-400-5338.596-50.4-0.80646.341-44.4440-10-10-55.556177.778-5990.244156.41-25-33.333-4050-55.5560-62.553.3334.348"'
union all
select '"27859K""FAMER""""PCH#(X(RI)~U$""1M)*1.000""""12-31-71""""06-30-11""""M""475""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""14.667-22.093161.19400-0.57136.782-20.168-7.895-2.857-52.94125254080.769-14.894-10-46.111-23.711-48.649-18.421-19.355-36-6.25126.667-23.529-3.846-32-35.294-9.091-3035.714-21.053-20-46.667-6.25133.333-14.286-23.3330-23.913-8.571-6.25-20016.667-28.571-30-14.28666.667-40-41.66714.286250-60-40040033.33375142.857-61.765-75.385290.625-1236.36433.333-15-35.294018.182-23.0770-19-12.346-1.408-28.57128-21.87548-45.94625-20-22.522.581-47.36800-25"'
union all
select '"30946M""FAMER""""PCH#(X(RI)~U$""1M)*1.000""""12-31-71""""06-30-11""""M""475""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""59.459-8.4751.8520.727-7.942-21.569158.6961.2-1.186-416.25-70.968-13.58-48.571-66.667-33.333-15-26.47120-41.667100-42.857158.6960-4-6.2511.11100-30-14.2860-33.33300-602580-19.444-10.345-23.0770-10-13.333-29.487-63.63620058.333-5.263-11.111-37.500260-72.222100-7492.308-1858.537-23.0772058.333-47.368"'
union all
select '"543906""FAMER""""PCH#(X(RI)~U$""1M)*1.000""""12-31-71""""06-30-11""""M""475""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""116.667-23.077-5000-20255033.3330-80100500-16.66700040-57.14333.33337.5-22.7270-5.882-6.2500000000000000000000000000000000000000000000000000000000000000000000000-87.20066.66720-16.66700-37.50000000605025.00100.610-97.576002500-65.385-44.444000-0.800-35.4840-2500-20000108.33412-17.8570-47.8260-16.667050233.334102-85.149-602500-28.571-33.333-3028.57100511.111-63.6360-2025-25-53.33300014.28600-25005000-44.44400000000000000001004500000-87.273000-28.5710000-60000000000000000000000000000-500000000000"'
union all
select '"35728E""FAMER""""PCH#(X(RI)~U$""1M)*1.000""""12-31-71""""06-30-11""""M""475""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""03.36518.6051.961-8.4621.6811.24-10.2044.5453.043-7.173-2.2733.2560-1.351-0.913-4.147-18.269011.7659.263-2.2160-1.478-10-16.667000-18.66722.9510-34-2.02-2.68-12.5-26.15-9.836-45.455083.3330000-18.182011.111-2037.5-5.455019.2310-6.4526.897-20.968"'
union all
select '"28152X""FAMER""""PCH#(X(RI)~U$""1M)*1.000""""12-31-71""""06-30-11""""M""475""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""""N/A""0001.92300-1.8875.3852.1917.857-1.2127.362-3.7142.671-1.7341.471-4.348-3.03-7.8133.39-1.6396.667-4.0630.977-3.2261012.121-2.703-2.7780-5.7142.970.059-100.327-0.6511.311-1.9425.6112.50.61-4.5451.587-8.1257.143-1.587-0.323-3.2360.3340-103.704-7.1435-19.78-3.65313.744-12.5-14.28610-38.889-0.82601.66731.311-5.743-7.28527.143-24.157-0.74115.67220.645-9.091-13.529-1.3619.517-3.023-1.9482.6499.677-14.70611.0342.48430.303-0.465-0.9354.717-12.1622.667-7.592"'
First columns is the header
We get this file from an external vendor and loaded the file as it is in a table as above.
Now the expected output is
"712232"|"FR"|"PCH1M)*1.000"||"12-31-71"|"06-30-11"|"M"|475| 1971 |N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|3.212
"712232"|"FR"|"PCH1M)*1.000"||"12-31-71"|"06-30-11"|"M"|475| 1972 |N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A
"712232"|"FR"|"PCH1M)*1.000"||"12-31-71"|"06-30-11"|"M"|475| 1973 |N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A
"712232"|"FR"|"PCH1M)*1.000"||"12-31-71"|"06-30-11"|"M"|475| 1974 |N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A
"712232"|"FR"|"PCH1M)*1.000"||"12-31-71"|"06-30-11"|"M"|475| 1975 |N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A
"712232"|"FR"|"PCH1M)*1.000"||"12-31-71"|"06-30-11"|"M"|475| 1976 |N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A
"712232"|"FR"|"PCH1M)*1.000"||"12-31-71"|"06-30-11"|"M"|475|1977|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A
...
...
...(upto the available last month end date in the input file)
"12121K"|"FR"|"PCH1M)*1.000"||"12-31-71"|"06-30-11"|"M"|475| 1971 |N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A
"12121K"|"FR"|"PCH1M)*1.000"||"12-31-71"|"06-30-11"|"M"|475| 1972 |0.211|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A
"12121K"|"FR"|"PCH1M)*1.000"||"12-31-71"|"06-30-11"|"M"|475| 1973 |N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A
"12121K"|"FR"|"PCH1M)*1.000"||"12-31-71"|"06-30-11"|"M"|475| 1974 |N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A
"12121K"|"FR"|"PCH1M)*1.000"||"12-31-71"|"06-30-11"|"M"|475| 1975 |N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A
"12121K"|"FR"|"PCH1M)*1.000"||"12-31-71"|"06-30-11"|"M"|475|1976|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A
...
...
...
...(upto the available last month end date in the input file)
"DATES ""INSTRUMENT(MNEMONIC)"" ""DATATYPES"" ""12-31-71"" ""06-30-11"" ""M"" 475
""12-31-71"" -- Start date of the first return column on the excel
""06-30-11"" -- End date of the last return column on the excel
475 -- number of columns in between start and end date
karthik
October 5, 2011 at 12:53 am
the important one is "dynamic column".. When we receive the same file next month we will have one more column in the file. i.e 31/oct/2011.
karthik
October 5, 2011 at 2:29 am
You have told us what you are doing.
Now take the next step and ask the question, or questions, to which you would like a response.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
October 5, 2011 at 3:45 am
I would like to get the below output from the table.
"712232"|"FR"|"PCH1M)*1.000"||"12-31-71"|"06-30-11"|"M"|475| 1971 |N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|3.212
"712232"|"FR"|"PCH1M)*1.000"||"12-31-71"|"06-30-11"|"M"|475| 1972 |N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A
"712232"|"FR"|"PCH1M)*1.000"||"12-31-71"|"06-30-11"|"M"|475| 1973 |N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A
"712232"|"FR"|"PCH1M)*1.000"||"12-31-71"|"06-30-11"|"M"|475| 1974 |N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A
"712232"|"FR"|"PCH1M)*1.000"||"12-31-71"|"06-30-11"|"M"|475| 1975 |N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A|N/A
karthik
October 5, 2011 at 3:55 am
There's still no hint of a question mark there.
If I said: "I would like a new car," would you know how to respond?
If I said: "Can you for help me choose where to buy a new car?" maybe you'd be able to offer some targeted advice, because now you know what I am looking for.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
October 5, 2011 at 4:15 am
sure. my question is how should i get the expected result from a single column. If you look at the table structure, it has only one column. But the expected result has lot of columns.
Note: It seems like i have to use PIVOT with TALLY table combination.
karthik
October 7, 2011 at 5:33 am
any inputs?
karthik
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply