Data transformations


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Data transformations
# 1  
Old 10-26-2011
Data transformations

hi all,

I have oracle table which has like below example in col7 & col8 there will be values seperated , in one column it is kind of horizontal pivoting but the data is in 1 column seprated ,
In near future this col7 and col8 may have some more values

col7 * col8 that many records it should create

[
Code:
col1| clo2| col3| col4| clo5 |col7 | col8 
ahy |ahx |axs |aht |azs |LO1,LO2,LO3,LO4 |LH1,LH2,LH3
zas |bsz |dgf |asd |ans |LO5,LO6 |LH7

I need the ouptput like
Code:
col1 clo2 col3 col4 clo5 col7   col8 
ahy ahx axs aht azs     LO1    LH1
ahy ahx axs aht azs     LO1    LH2
ahy ahx axs aht azs     LO1    LH3
ahy ahx axs aht azs     LO2    LH1
ahy ahx axs aht azs     LO2    LH2
ahy ahx axs aht azs     LO2    LH3
ahy ahx axs aht azs     LO3    LH1
ahy ahx axs aht azs     LO3    LH2
ahy ahx axs aht azs     LO3    LH3
ahy ahx axs aht azs     LO4    LH1
ahy ahx axs aht azs     LO4    LH2
ahy ahx axs aht azs     LO4    LH3
zas bsz dgf asd ans     LO5    LH7
zas bsz dgf asd ans     LO6    LH7

any script like ksh or perl would be help full to start the 1 step which that script can create a file so that i can go to the 2nd step Thank you very much
Moderator's Comments:
Mod Comment
Please use code tags when posting data and code samples!

Last edited by marpadga18; 10-26-2011 at 12:39 PM.. Reason: code tags, please!
# 2  
Old 10-26-2011
Code:
#!/usr/bin/ksh
IFS='|'
while read m1 m2 m3 m4 m5 m6 m7; do
  IFS=','
  for m6A in ${m6}; do
    for m7A in ${m7}; do
      echo ${m1} ${m2} ${m3} ${m4} ${m5} ${m6A} ${m7A}
    done
  done
  IFS='|'
done < Inp_File

Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Parsing XML (and insert data) then output data (bash / Solaris)

Hi folks I have a script I wrote that basically parses a bunch of config and xml files works out were to add in the new content then spits out the data into a new file. It all works - apart from the xml and config file format in the new file with XML files the original XML (that ends up in... (2 Replies)
Discussion started by: dfinch
2 Replies

2. UNIX for Dummies Questions & Answers

Log transformations with awk

Hi, I have a very large csv file with almost 100,000 columns and 200 rows Example.csv ID,field1,field2,field3,field4 A,5,6,7,8 B,1,2,3,4 C,3,5,6,7 I'm trying to find a way, using awk, to log (base 2) all the values in the table (minus the headers and id field) Output ... (5 Replies)
Discussion started by: nemo246
5 Replies

3. Shell Programming and Scripting

Generate tabular data based on a column value from an existing data file

Hi, I have a data file with : 01/28/2012,1,1,98995 01/28/2012,1,2,7195 01/29/2012,1,1,98995 01/29/2012,1,2,7195 01/30/2012,1,1,98896 01/30/2012,1,2,7083 01/31/2012,1,1,98896 01/31/2012,1,2,7083 02/01/2012,1,1,98896 02/01/2012,1,2,7083 02/02/2012,1,1,98899 02/02/2012,1,2,7083 I... (1 Reply)
Discussion started by: himanish
1 Replies

4. Shell Programming and Scripting

Converting variable space width data into CSV data in bash

Hi All, I was wondering how I can convert each line in an input file where fields are separated by variable width spaces into a CSV file. Below is the scenario what I am looking for. My Input data in inputfile.txt 19 15657 15685 Sr2dReader 107.88 105.51... (4 Replies)
Discussion started by: vharsha
4 Replies

5. Shell Programming and Scripting

Matching and replacing text with case transformations

Hi, I am trying to edit an XML file automatically but my regex and shell script knowledge is very limited. I would appreciate your help fellows. The XML file has this structure: <?xml version="1.0" encoding="UTF-8"?> <map map_file="maps/world.swf" zoom="350%" zoom_x="-115%"... (1 Reply)
Discussion started by: boonymagique
1 Replies

6. Shell Programming and Scripting

Extract data based on match against one column data from a long list data

My input file: data_5 Ali 422 2.00E-45 102/253 140/253 24 data_3 Abu 202 60.00E-45 12/23 140/23 28 data_1 Ahmad 256 7.00E-45 120/235 140/235 22 data_4 Aman 365 8.00E-45 15/65 140/65 20 data_10 Jones 869 9.00E-45 65/253 140/253 18... (12 Replies)
Discussion started by: patrick87
12 Replies

7. Shell Programming and Scripting

how to verify that copied data to remote system is identical with local data.

I have created simple shell script #!/bin/sh echo `date`; echo "Start .... find . -mtime +95 -print > /tmp/files.txt for file in `cat /tmp/files.txt` do echo "copying file - $file" /usr/local/bin/scp -p -P 2222 $file remote.hostname:/file/path echo "copid file -... (3 Replies)
Discussion started by: ynilesh
3 Replies

8. UNIX for Dummies Questions & Answers

Howto capture data from rs232port andpull data into oracle database-9i automatically

Hi, i willbe very much grateful to u if u help me out.. if i simply connect pbx machine to printer by serial port RS232 then we find this view: But i want to capture this data into database automatically when the pbx is running.The table in database will contain similar to this view inthe... (1 Reply)
Discussion started by: boss
1 Replies

9. UNIX for Dummies Questions & Answers

Numerical System Transformations - Lecture

Hy, I wrote new article on Numerical Systems and Transformations yesterday evening (on my C Lessons Project webpage) and would like to share it with you. If someone can help me format my text for this forum, I would appreciate it, so I culd post full article here.. Here's intro: "Our... (0 Replies)
Discussion started by: vurdlak
0 Replies
Login or Register to Ask a Question