Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Implementing Listagg like function in shell

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Implementing Listagg like function in shell
# 1  
Old 06-27-2016
Implementing Listagg like function in shell
Hi,

Basically what I am trying to do is making multiple fields of the same type comma-separated.

i.e. for a data like this:
Code:
B00000	abc
B00001	abc,def
B00001	ghi
B00001	jkl
B00002	abc
B00002	def
B00003	xyz

Output should be like:
Code:
B00000	abc
B00001	abc,def,ghi,jkl
B00002	abc,def
B00003	xyz

I found a solution on stackoverflow for a very similar problem here

but none of the specified solutions in there work for me. They just print whatever the input is given, as it is.

Any ideas on what's wrong and how can I make it work?

Code:
#!/usr/bin/awk -f
BEGIN {
    FS="\t"; OFS=FS
    deduplist=ARGV[1]
    ARGV[1]=""
    split(deduplist,tmp," ")
    for (i in tmp) dedup[tmp[i]]=1
}
{
    for (i=1; i<=NF;i++)
        if (i in dedup) {
            if ($i == prev[i])
                $i = ""
            else
                prev[i] = $i
        }
    # prevent printing lines that are completely blank because 
    # it's an exact duplicate of the preceding line and all fields 
    # are being deduplicated
    if ($0 !~ /^[[:blank:]]*$/) 
        print
}

Thanks.

Your help is greatly appreciated.
# 2  
Old 06-27-2016
No surprise it doesn't work for you, as it by no means fits the problem. Try

Code:
awk '{F[$1] = F[$1] DL[$1] $2; DL[$1] = ","} END {for (f in F) print f, F[f]}' file
B00000 abc
B00001 abc,def,ghi,jkl
B00002 abc,def
B00003 xyz

As the order in which the array elements are retrieved is unspecified, you may need to sort the output. And, you may want to define a different output field separator.
This User Gave Thanks to RudiC For This Post:
prohank
# 3  
Old 06-28-2016
Thanks @RudiC that worked great.

Sorry for the late reply. I didn't get time yesterday to check the code.

Can we make it more generalised like passing arguments such as in the example since I have some results with multiple columns in between.

Thanks again. I know its a piece of cake for you.
# 4  
Old 06-28-2016
You need to become more specific. As I said, the example DOESN'T fit.
# 5  
Old 06-28-2016
What I mean is, some tables are like:

Code:
B00000	1	...	abc
B00001	1	...	abc,def
B00001	2	...	ghi
B00001	2	...	jkl
B00002	1	...	abc
B00002	2	...	def
B00003	1	...	xyz

Can we edit the code such that we can pass which columns should be comma-separated and which not?

In the table that I gave now, I may not need to remove duplicates from the second column but only from the last column or vice versa.

Possible solutions are:
1.
Code:
B00000	1	...	abc
B00001	1,2	...	abc,def,ghi,jkl
B00002	1,2	...	abc,def
B00003	1	...	xyz

2.
Code:
B00000	1	...	abc
B00001	1	...	abc,def
B00001	2	...	ghi,jkl
B00002	1	...	abc
B00002	2	...	def
B00003	1	...	xyz

Basically, the syntax would be like:
Code:
awk 'command' arguments(specifying columns to be comma-separated) file

Thanks.
# 6  
Old 06-28-2016
Those two requests are different: The first one has the columns aggregated with unique key $1, the second with key $1 $2.
What about the other columns (indicated by "..." in your sample)? Which contents should be retained?
# 7  
Old 06-28-2016
Yes that is exactly the question.

Can't we have a more generalised command that cover all the above scenarios?
i.e specify which column to aggregate with and which to comma separate.

Am I being clear or is this too complex?
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

DB2 Query modification to remove duplicate values using LISTAGG function

I am using DB2 v9 and trying to get country values in comma seperated format using below query SELECT distinct LISTAGG(COUNTRIES, ',') WITHIN GROUP(ORDER BY EMPLOYEE) FROM LOCATION ; Output Achieved MEXICO,UNITED STATES,INDIA,JAPAN,UNITED KINGDOM,MEXICO,UNITED STATES The table... (4 Replies)
Discussion started by: Perlbaby
4 Replies

2. Shell Programming and Scripting

Implementing linked list in shell scripting

Hello Experts, Is it possible to implement linked list in shell scripting? is yes then how can we do it? Any working example is highly appreciated. Thanks in advance. (4 Replies)
Discussion started by: mukulverma2408
4 Replies

3. Shell Programming and Scripting

Need help implementing a timout in my Shell Script for RHEL6

Hey Guys, My problem: I have a script that will be querying the database every minute to see if it gets a response, the response its querying for is "UP" in a table i made called dbup in the database. Now, I am trying to add the component to implement a timeout if the script does not get a... (2 Replies)
Discussion started by: mo_VERTICASQL
2 Replies

4. Programming

Implementing function outside struct

I have this code where I have declared a struct with some functions. Trying to write the function implementation outside the struct declaration and do not know how to proceed. #ifndef ParseEl_hh #define ParseEl_hh #include <iostream> #include <fstream> #include "DynBaseObj.hh"... (7 Replies)
Discussion started by: kristinu
7 Replies

5. Programming

Problem with implementing the times() function in C (struct tms times return zero/negative values)

Hello, i'm trying to implement the times() function and i'm programming in C. I'm using the "struct tms" structure which consists of the fields: The tms_utime structure member is the CPU time charged for the execution of user instructions of the calling process. The tms_stime structure... (1 Reply)
Discussion started by: g_p
1 Replies

6. Shell Programming and Scripting

Implementing Queue Using Shell scripts

HI I want to implement a control mechanism using Shell scripts .The intention is to have controlled number of jobs running in parallel External process will kickstart 40 jobs in parallel .All the 40 jobs will call the same generic script with different parameter values .But at a... (4 Replies)
Discussion started by: police
4 Replies

7. Homework & Coursework Questions

implementing mkdir, chdir, mv, pwd inside a shell !

1. The problem statement, all variables and given/known data: need to implement mkdir, chdir, mv, pwd given a shell.cpp directory.cpp and some other files this shell missing these commands, and i need to implement them inside the shell 2. Relevant commands, code, scripts,... (0 Replies)
Discussion started by: evantheking
0 Replies

8. Programming

Implementing a shell in C

Hi, I am implementing a shell in C, with the following problem... Suppose the shell is invoked from the command line as >> myshell < test.in > test.out 2>&1 I have to execute the commands in test.in and redirect them to test.out How does one detect in the main function that the shell... (1 Reply)
Discussion started by: jacques83
1 Replies

9. Programming

need help in implementing simple interactive shell in C

hello all, i hv attached herewith my program to implement a simple interactive shell in C. no matter hw hard I try, I keep getting some errors. i need help - urgently !! proj1test7.c: In function `parseCommand': proj1test7.c:102: warning: assignment makes pointer from integer without a cast... (2 Replies)
Discussion started by: nix1209
2 Replies

10. Programming

Implementing a shell

I'm implementing a shell in C that supports piping, output redirection, and background processing, and a few other commands. I was wondering how I'd go about implementing the output redirection. So, I'd open a file and I'd fork and execute the command. But how would I get stdout into the file? Any... (10 Replies)
Discussion started by: ununium
10 Replies
Login or Register to Ask a Question