Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: Join columns across multiple lines in a Text based on common column using BASH
Top Forums Shell Programming and Scripting Join columns across multiple lines in a Text based on common column using BASH Post 303014167 by RudiC on Tuesday 6th of March 2018 07:45:14 AM
Old 03-06-2018
How about (tested on linux with mawk 1.3.3)
Code:
awk -F\| '
        {TMP[$2] = TMP[$2] FA[$2] $1
         FA[$2] = FS
        }
END     {CH[1] = 97
         for (t in TMP) {n = split (TMP[t], T)
                         printf "select * from %s %c", T[1], CH[1]
                         for (i=2; i<=n; i++)   {CH[i] = CH[i-1] + 1
                                                 printf " inner join %s %c on %c.%s=%c.%s", T[i], CH[i], CH[1], t, CH[i], t
                                                }
                         printf RS
                        }
        }
' file
select * from Table1 a inner join Table2 b on a.Column1=b.Column1 inner join Table5 c on a.Column1=c.Column1
select * from Table3 a inner join Table2 b on a.Column2=b.Column2
select * from Table4 a inner join Table2 b on a.Column3=b.Column3
select * from Table2 a inner join Table5 b on a.Column4=b.Column4
select * from Table2 a

EDIT: or mayhap with a simpler END section:
Code:
END     {split ("a b c d e f g", CH, " ")
         for (t in TMP) {n = split (TMP[t], T)
                         printf "select * from %s %c", T[1], CH[1]
                         for (i=2; i<=n; i++)   printf " inner join %s %c on %c.%s=%c.%s", T[i], CH[i], CH[1], t, CH[i], t
                         printf RS
                        }

Last edited by RudiC; 03-06-2018 at 08:53 AM..
This User Gave Thanks to RudiC For This Post:
nv186000
 

10 More Discussions You Might Find Interesting

1. UNIX for Dummies Questions & Answers

find common lines using just one column to compare and result with all columns

Hi. If we have this file A B C 7 8 9 1 2 10 and this other file A C D F 7 9 2 3 9 2 3 4 The result i´m looking for is intersection with A B C D F so the answer here will be (10 Replies)
Discussion started by: alcalina
10 Replies

2. Shell Programming and Scripting

sum multiple columns based on column value

i have a file - it will be in sorted order on column 1 abc 0 1 abc 2 3 abc 3 5 def 1 7 def 0 1 -------- i'd like (awk maybe?) to get the results (any ideas)??? abc 5 9 def 1 8 (2 Replies)
Discussion started by: jjoe
2 Replies

3. Shell Programming and Scripting

"Join" or "Merge" more than 2 files into single output based on common key (column)

Hi All, I have working (Perl) code to combine 2 input files into a single output file using the join function that works to a point, but has the following limitations: 1. I am restrained to 2 input files only. 2. Only the "matched" fields are written out to the "matched" output file and... (1 Reply)
Discussion started by: Katabatic
1 Replies

4. Shell Programming and Scripting

Join multiple files based on 1 common column

I have n files (for ex:64 files) with one similar column. Is it possible to combine them all based on that column ? file1 ax100 20 30 40 ax200 22 33 44 file2 ax100 10 20 40 ax200 12 13 44 file2 ax100 0 0 4 ax200 2 3 4 (9 Replies)
Discussion started by: quincyjones
9 Replies

5. Shell Programming and Scripting

join files based on a common field

Hi experts, Would you please help me with this? I have several files and I need to join the forth field of them based on the common first field. here's an example... first file: 280346 39.88 -75.08 547.8 280690 39.23 -74.83 538.7 280729 40.83 -75.08 499.2 280907 40.9 -74.4 507.8... (5 Replies)
Discussion started by: GoldenFire
5 Replies

6. UNIX for Dummies Questions & Answers

How to use the the join command to join multiple files by a common column

Hi, I have 20 tab delimited text files that have a common column (column 1). The files are named GSM1.txt through GSM20.txt. Each file has 3 columns (2 other columns in addition to the first common column). I want to write a script to join the files by the first common column so that in the... (5 Replies)
Discussion started by: evelibertine
5 Replies

7. UNIX for Dummies Questions & Answers

How to join 2 .txt files based on a common column?

Hi all, I'm trying to join two .txt file tab delimitated based on a common column. File 1 transcript_id gene_id length effective_length expected_count TPM FPKM IsoPct comp1000201_c0_seq1 comp1000201_c0 337 183.51 0.00 0.00 0.00 0.00 comp1000297_c0_seq1 ... (1 Reply)
Discussion started by: alisrpp
1 Replies

8. Shell Programming and Scripting

Join common patterns in multiple lines into one line

Hi I have a file like 1 2 1 2 3 1 5 6 11 12 10 2 7 5 17 12 I would like to have an output as 1 2 3 5 6 10 7 11 12 17 any help would be highly appreciated Thanks (4 Replies)
Discussion started by: Harrisham
4 Replies

9. Shell Programming and Scripting

Join multiple lines from text file

Hi Guys, Could you please advise how to join multiple details lines into single row, with HEADER 1 as the record separator and comma(,) as the field separator. Input: HEADER 1, HEADER 2, HEADER 3, 11,22,33, COLUMN1,COLUMN2,COLUMN3, AA1, BB1, CC1, END: ABC HEADER 1, HEADER 2,... (3 Replies)
Discussion started by: budz26
3 Replies

10. Shell Programming and Scripting

Paste columns based on common column: multiple files

Hi all, I've multiple files. In this case 5. Space separated columns. Each file has 12 columns. Each file has 300-400K lines. I want to get the output such that if a value in column 2 is present in all the files then get all the columns of that value and print it side by side. Desired output... (15 Replies)
Discussion started by: genome
15 Replies

LEARN ABOUT DEBIAN

shtool-arx

SHTOOL-ARX.TMP(1)					      GNU Portable Shell Tool						 SHTOOL-ARX.TMP(1)

NAME

       shtool-arx - GNU shtool ar(1) extensional command

SYNOPSIS

       shtool arx [-t|--trace] [-C|--command cmd] op archive file [file ...]

DESCRIPTION

       shtool arx is a wrapper around the archiving tool ar(1). It provides the ability to create archives out of existing archives, i.e., if one
       of file matches ""*.a"", the archive member files of file are used instead of file itself.

       The trick of this command is the automatic handling of archive members which is especially interesting if one wants to construct a (usually
       top-level) library archive out of pre-built sub-library archives (usually staying inside subdirs) in a large source tree. For GNU libtool
       based projects, a similar functionality is provided by GNU libtool internally, too.

OPTIONS

       The following command line options are available.

       -t, --trace
	   Shows the actually involved shell commands.

       -C, --command cmd
	   Set the used ar(1) command to cmd instead of just "ar" (searched in $PATH).

EXAMPLE

	#   Makefile
	AR=ar
	RANLIB=ranlib
	  :
	OBJS=foo.o bar.o
	LIBS=baz/libbaz.a quux/libquux.a
	  :
	libfoo.a: $(OBJS) $(LIBS)
	    shtool arx -C $(AR) rc libfoo.a $(OBJS) $(LIBS)
	    $(RANLIB) libfoo.a

HISTORY

       The GNU shtool arx command was originally written by Ralf S.  Engelschall <rse@engelschall.com> in 1999 for GNU shtool. It was prompted by
       need to build libraries out of sub-libraries inside the OSSP project.

SEE ALSO

       shtool(1), ar(1).

18-Jul-2008							   shtool 2.0.8 						 SHTOOL-ARX.TMP(1)
All times are GMT -4. The time now is 02:45 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy