Make or use delimiter to separate results

Question

I have SQL output with multiple results:

 1 OI 021 141012 1321 0001242 4S 2 0080004 5 001 00014 6 000001 000000000000016973 6 0529540437 0000000000
2963526113
test 7 8 000000001 9
 1 OI 021 141012 1321 0001242 4S 2 0080004 5 001 00014 6 000001 000000000000016973 6 0529540437 0000000000
2963526113
test 7 8 000000001 9

The question is how to make delimiter or something else and to join every second and third row to the first and turn every SQL query to one row them in that output:

1 OI 021 141012 1321 0001242 4S 2 0080004 5 001 00014 6 000001 000000000000016973 6 0529540437 00000000002963526113 test 7 8 000000001 9
1 OI 021 141012 1321 0001242 4S 2 0080004 5 001 00014 6 000001 000000000000016973 6 0529540437 00000000002963526113 test 7 8 000000001 9

This is the same question as you asked an hour ago: [Move sql output data to one row](http://stackoverflow.com/questions/26911378/move-sql-output-data-to-one-row) — ThisSuitIsBlackNot, Nov 13 '14 at 16:34

nu11p01n73R · Answer 1 · 2014-11-13T15:39:25.210

2

Awk love these !!

$ awk 'NR%3 == 0{print line;line=""; next} {line = line $0}' test
1 OI 021 141012 1321 0001242 4S 2 0080004 5 001 00014 6 000001 000000000000016973 6 0529540437 00000000002963526113
1 OI 021 141012 1321 0001242 4S 2 0080004 5 001 00014 6 000001 000000000000016973 6 0529540437 00000000002963526113

What it does??

NR%3 == 0 Checks if the number of records, NR is a multiple of 3 if yes prints the entire content of line variable. Resets the line line variable and next causes it to read the next record
{line = line $0} will be excecuted for NR is not a multiple of 3, copies the entire line $0 onto line variable

OR

much simpler

$ awk '!(NR%3){print line;line=""; next} {line = line $0}' test

edited Nov 13 '14 at 15:39

answered Nov 13 '14 at 15:14

nu11p01n73R

26,397
3
39
52

Could you tell me please in that case what is the meaning of "%" in NR%3, also seems not everything is included – Kalin Borisov Nov 13 '14 at 15:16
@KalinBorisov I have added an explanation. Hope its clear now – nu11p01n73R Nov 13 '14 at 15:21
2

Note `NR%3 == 0` is a bit too much explicit: `!(NR%3)` is enough. – fedorqui Nov 13 '14 at 15:35
1

@fedorqui i didn't think in that direction. Good point. Added in the answer. Thank you for the suggestion – nu11p01n73R Nov 13 '14 at 15:40

score 1 · Accepted Answer · answered Nov 13 '14 at 15:19

You can use paste with as many - as lines you want to join:

paste - - - < file

or

... | paste - - -

It returns:

I 021 141012 1321 0001242 4S 2 0080004 5 001 00014 6 000001 000000000000016973 6 0529540437 0000000000  2963526113  test 7 8 000000001 9
1 OI 021 141012 1321 0001242 4S 2 0080004 5 001 00014 6 000001 000000000000016973 6 0529540437 0000000000   2963526113  test 7 8 000000001 9

If you need to use another delimiter instead of space, use -d: for example, paste -d"|" - - - <file.

score 1 · Answer 3 · answered Nov 13 '14 at 15:35

Using gnu-awk and custom RS:

awk -v RS='\n[0-9]+ [A-Z]+ [0-9]+' '{gsub(/\n/, "")} NR==1{print; p=RT; next} 
        {gsub(/\n/, "", p); print p $0; p=RT}' file
1 OI 021 141012 1321 0001242 4S 2 0080004 5 001 00014 6 000001 000000000000016973 6 0529540437 00000000002963526113test 7 8 000000001 9
1 OI 021 141012 1321 0001242 4S 2 0080004 5 001 00014 6 000001 000000000000016973 6 0529540437 00000000002963526113test 7 8 000000001 9

PS: This will work for records that are broken out it in any number of lines as long as starting fields have this regex pattern: [0-9]+ [A-Z]+ [0-9]+'

Make or use delimiter to separate results

3 Answers3