Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Convert Rows into Column

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Convert Rows into Column
# 1  
Old 12-12-2013
Convert Rows into Column
Hi Experts,

I have a requirement to convert rows into columns.

For e.g.

Input File:
Quote:
abcd|201301|125
abcd|201303|266
defg|201301|250
defg|201302|450
defg|201303|560
Output File should be like
Quote:
abcd|125||266
defg|250|450|560
Appreciate if you could suggest code snippet(may be awk) for above requirement...

Thanks in Advance for your help...
# 2  
Old 12-12-2013
Try

Code:
awk -F "|" '{A[$1]=A[$1]?A[$1]"|"$NF:$1"|"$NF;next}END{for(i in A) {print A[i]}}' file

Last edited by pamu; 12-12-2013 at 07:29 AM.. Reason: Corrected.. Thanks Akshay :)
# 3  
Old 12-12-2013
Please post in proper sub forum next time this question is not about OS

Quote:
Originally Posted by pamu
Try

Code:
awk -F "|" '{A[$1]=A[$1]?A[$1]"|"$NF:$1"|"$NF;next}{for(i in A) {print A[i]}}' file

Pamu seems to be not working...END missing Smilie

Code:
awk -F "|" '{A[$1]=A[$1]?A[$1]"|"$NF:$1"|"$NF;next}END{for(i in A) {print A[i]}}' file

if your file is sorted try this

Code:
$ awk 'NR!=1 && p!=$1{print s;s=""}{s = s ? s OFS $NF : $1 OFS $NF;p=$1}END{print s}' FS="|" OFS="|" file
abcd|125|266
defg|250|450|560

Last edited by Akshay Hegde; 12-12-2013 at 07:27 AM..
This User Gave Thanks to Akshay Hegde For This Post:
sai_2507
# 4  
Old 12-12-2013
Try also
Code:
awk '{T[$1]} $1 in T {T[$1]=T[$1] "|" $NF} END {for (i in T) print i T[i]}' 'FS=|' file
abcd|125|266
defg|250|450|560

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Programming

DB2 Query -Convert multi values from column to rows

Hi Team I am using DB2 artisan tool and struck to handle multi values present in columns that are comma(,) separated. I want to convert those column values in separate rows . For example : Column 1 Column2 Jan,Feb Hold,Sell,Buy Expected Result Column1 ... (3 Replies)
Discussion started by: Perlbaby
3 Replies

2. Shell Programming and Scripting

Convert rows into column along with header

Hi, I have a requirement to format the data in a new order. Here is my source format : ppp ***Wed Dec 16 10:32:30 GMT 2015 header1 header2 header3 header4 header5 server1 0.00 0.02 0.07 0.98 server2 0.01 0.00 0.08 0.79 server3 0.05 0.82 0.77 0.86 ... (18 Replies)
Discussion started by: john_prince
18 Replies

3. Shell Programming and Scripting

Convert Column data values to rows

Hi all , I have a file with the below content Header Section employee|employee name||Job description|Job code|Unitcode|Account|geography|C1|C2|C3|C4|C5|C6|C7|C8|C9|Csource|Oct|Nov|Dec|Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep Data section ... (1 Reply)
Discussion started by: Hypesslearner
1 Replies

4. Shell Programming and Scripting

Convert rows to column and add header

Hi, I need help to convert rows in input file into a table. inputfile 192.98.1 192.98.192.98.17 VVC family Zorro 10 192.98.1 192.98.192.98.17 VVC family Ace 1 192.98.1 192.98.192.98.17 VVC family ... (4 Replies)
Discussion started by: redse171
4 Replies

5. Shell Programming and Scripting

Convert single column into multiple rows

Convert Single column to multiple rows file a.txt contains data like below Server=abc Run=1 Tables=10 Sessions=16 Time=380 Jobs=5 Server=abc Run=2 Tables=15 Sessions=16 Time=400 Jobs=5 Server=abc Run=3 Tables=20 Sessions=16 Time=450 (5 Replies)
Discussion started by: sol_nov
5 Replies

6. Shell Programming and Scripting

convert columns into rows with respect to first column

Hello All, Please help me with this file. My input file (Tab separated) is like: Abc-01 pc1 -0.69 Abc-01 E2cR 0.459666666666667 Abc-01 5ez.2 1.2265625 Xyz-01 pc1 -0.153 Xyz-01 E2cR 1.7358 Xyz-01 5ez.2 2.0254 Ced-02 pc1 -0.5714 Ced-02 ... (7 Replies)
Discussion started by: mira
7 Replies

7. Shell Programming and Scripting

Convert rows into column groups

Hi I have the text file like this "A" "AA Info" "AA Text" "AAA" "ABC" "ABC Info" "ABC Tech" "AGH" "SYN" "SYMBony" "SYN BEREN" Like about 2000 lines Output would be in Column with groups like following "A" "AA Info", "AA Text" "AAA" "ABC","ABC Info","ABC Tech" (0 Replies)
Discussion started by: selvanraj
0 Replies

8. Shell Programming and Scripting

Convert Column to rows

Hi, I have a file with below contents. Heading1 Heading2 Heading3 Heading4 Value1 Value2 Value3 Value4 The file has only 2 rows and is tab separated The desired output is : Heading1 Value1 Heading2 Value2 Heading3 Value3 Heading4 Value4 CAn you please help? (5 Replies)
Discussion started by: kaponeh
5 Replies

9. UNIX for Dummies Questions & Answers

How to convert a single column into several rows and columns?

I have a program which gives me the output as a single column with hundreds of rows like: 213 314 324 324 123 I want to be able to create a new file from this file which allows me to set the number of rows and columns in the new file, i.e. for this example, if I specify 3 rows and 2... (5 Replies)
Discussion started by: ashton_smith
5 Replies

10. Shell Programming and Scripting

convert rows into column

if u have a data 2 4 6 8 5 4 4 5 6 then result shud be like 2 4 6 7 5 4 4 5 6 (3 Replies)
Discussion started by: cdfd123
3 Replies
Login or Register to Ask a Question