Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: DB2 Query modification to remove duplicate values using LISTAGG function
Top Forums UNIX for Beginners Questions & Answers DB2 Query modification to remove duplicate values using LISTAGG function Post 303036866 by Perlbaby on Monday 15th of July 2019 05:53:36 AM
Old 07-15-2019
Hi Corona688 Thank you for quick help on this . However I would need further info on the handling scenarios when we have multiple column in a table .

Code:
 
 SELECT
 Employee_ID 
 LISTAGG(COUNTRIES_OUT, ',') WITHIN GROUP(ORDER BY Employee_ID) AS "COUNTRIES_OUT" 
 FROM 
 LOCATION 
 GROUP BY 
 Employee_ID
 Output 
 
 Employee_ID ,               COUNTRIES_OUT 
 1234                    MEXICO,UNITED STATES, INDIA, JAPAN,UNITED KINGDOM,PHILIPPINES

The above observation is fine as I am using only one column "COUNTRIES_OUT" and grouped against one column "Employee_ID"
Now the challenge is when I add new column to replicate same logic

Code:
 
 SELECT
 Employee_ID 
 LISTAGG(COUNTRIES_OUT, ',') WITHIN GROUP(ORDER BY Employee_ID) AS "COUNTRIES_OUT" ,
 LISTAGG(COUNTRIES_IN, ',') WITHIN GROUP(ORDER BY Employee_ID) AS "COUNTRIES_IN"  ( This is newly added which has only one country ) 
 FROM 
 LOCATION 
 GROUP BY 
 Employee_ID
 Output 
 Employee_ID ,                                                    COUNTRIES_OUT                                                                                                                    COUNTRIES_IN 
 1234                  MEXICO,UNITED STATES, INDIA, JAPAN,UNITED KINGDOM,PHILIPPINES                                              UNITED STATES,UNITED STATES,UNITED STATES,UNITED STATES,UNITED STATES,UNITED STATES

The above output give 6 times UNITED STATES for column COUNTRIES_IN even though it appears once .
Code:
 
 How can I get the output like below 
 
 Employee_ID ,                                                    COUNTRIES_OUT                                                                                                                    COUNTRIES_IN 
 1234                  MEXICO,UNITED STATES, INDIA, JAPAN,UNITED KINGDOM,PHILIPPINES                                                                                     UNITED STATES

Please guide . any help appreciated
 

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Db2 query with script

Hi All, I want to connect two tables in DB2 using shell script and then compare the contents of two tables field by field.and i should return on the screen the un matched records .. Could any one please help me in connecting database tables using Unix and retriving data from the same. (1 Reply)
Discussion started by: kanakaraju
1 Replies

2. UNIX for Dummies Questions & Answers

[SOLVED] remove lines that have duplicate values in column two

Hi, I've got a file that I'd like to uniquely sort based on column 2 (values in column 2 begin with "comp"). I tried sort -t -nuk2,3 file.txtBut got: sort: multi-character tab `-nuk2,3' "man sort" did not help me out Any pointers? Input: Output: (5 Replies)
Discussion started by: pathunkathunk
5 Replies

3. Shell Programming and Scripting

awk file to read values from Db2 table replacing hard coded values

Hi, I want to replace a chain of if-else statement in an old AWK file with values from Db2 table or CSV file. The part of code is below... if (start_new_rec=="true"){ exclude_user="false"; user=toupper($6); match(user, "XXXXX."); if (RSTART ==2 ) { ... (9 Replies)
Discussion started by: asandy1234
9 Replies

4. Shell Programming and Scripting

Remove duplicate values with condition

Hi Gents, Please can you help me to get the desired output . In the first column I have some duplicate records, The condition is that all need to reject the duplicate record keeping the last occurrence. But the condition is. If the last occurrence is equal to value 14 or 98 in column 3 and... (2 Replies)
Discussion started by: jiam912
2 Replies

5. Shell Programming and Scripting

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: B00000 abc B00001 abc,def B00001 ghi B00001 jkl B00002 abc B00002 def B00003 xyz Output should be like: B00000 abc B00001 abc,def,ghi,jkl... (20 Replies)
Discussion started by: prohank
20 Replies

6. Shell Programming and Scripting

Filter file to remove duplicate values in first column

Hello, I have a script that is generating a tab delimited output file. num Name PCA_A1 PCA_A2 PCA_A3 0 compound_00 -3.5054 -1.1207 -2.4372 1 compound_01 -2.2641 0.4287 -1.6120 3 compound_03 -1.3053 1.8495 ... (3 Replies)
Discussion started by: LMHmedchem
3 Replies

7. Shell Programming and Scripting

Remove duplicate values in a column(not in the file)

Hi Gurus, I have a file(weblog) as below abc|xyz|123|agentcode=sample code abcdeeess,agentcode=sample code abcdeeess,agentcode=sample code abcdeeess|agentadd=abcd stereet 23343,agentadd=abcd stereet 23343 sss|wwq|999|agentcode=sample1 code wqwdeeess,gentcode=sample1 code... (4 Replies)
Discussion started by: ratheeshjulk
4 Replies

8. Shell Programming and Scripting

Find duplicate values in specific column and delete all the duplicate values

Dear folks I have a map file of around 54K lines and some of the values in the second column have the same value and I want to find them and delete all of the same values. I looked over duplicate commands but my case is not to keep one of the duplicate values. I want to remove all of the same... (4 Replies)
Discussion started by: sajmar
4 Replies

9. 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

10. Programming

DB2 Query to pick hierarchy values

Dear Team I am using DB2 v9 . I have a condition to check roles based on hierarchies like below example. 1.Ramesh has Roles as "Manager" and "Interviewer" 2.KITS has Roles as "Interviewer" 3.ANAND has Roles as "Manager" and "Interviewer" select * FROM TESTING NAME ... (6 Replies)
Discussion started by: Perlbaby
6 Replies

LEARN ABOUT DEBIAN

fntsample

fntsample(1)						      General Commands Manual						      fntsample(1)

NAME

       fntsample - PDF and PostScript font samples generator

SYNOPSIS

       fntsample [ OPTIONS ] -f FONT-FILE -o OUTPUT-FILE
       fntsample -h

DESCRIPTION

       fntsample  program  can	be  used  to generate font samples that show Unicode coverage of the font and are similar in appearance to Unicode
       charts. Samples can be saved into PDF (default) or PostScript file.

OPTIONS

       fntsample supports the following options.

       --font-file, -f FONT-FILE
	      Make samples of FONT-FILE.

       --font-index, -n IDX
	      Font index for FONT-FILE specified using --font-file option.  Useful for files that contain multiple fonts,  like  TrueType  Collec-
	      tions (.ttc).  By default font with index 0 is used.

       --output-file, -o OUTPUT-FILE
	      Write output to OUTPUT-FILE.

       --other-font-file, -d OTHER-FONT
	      Compare FONT-FILE with OTHER-FONT.  Glyphs added to FONT-FILE will be highlighted.

       --other-index, -m IDX
	      Font index for OTHER-FONT specified using --other-font-file option.

       --postscript-output, -s
	      Use PostScript format for output instead of PDF.

       --svg, -g
	      Use SVG format for output.  The generated document contains one page.  Use range selection options to specify which.

       --print-outline, -l
	      Print  document  outlines data to standard output.  This data can be used to add outlines (aka bookmarks) to resulting PDF file with
	      pdfoutline program.

       --include-range, -i RANGE
	      Show characters in RANGE.

       --exclude-range, -x RANGE
	      Do not show characters in RANGE.

       --style, -t "STYLE: VAL"
	      Set STYLE to value VAL.  Run fntsample with option --help to see list of styles and default values.

       --help, -h
	      Display help text and exit.

       Parameter RANGE for -i and -x can be given as one integer or a pair of integers delimited by minus sign (-).  Integers can be specified	in
       decimal,  hexadecimal  (0x...) or octal (0...) format.  One integer of a pair can be missing (-N can be used to specify all characters with
       codes less or equal to N, and N- for all characters with codes greather or equal to N).	Multiple -i and -x options can be used.

EXAMPLES

       Make PDF samples for font.ttf and write them to file samples.pdf:
	      fntsample -f font.ttf -o samples.pdf

       Make PDF samples for font.ttf, compare it with oldfont.ttf and highlight new glyphs. Write output to file samples.pdf:
	      fntsample -f font.ttf -d oldfont.ttf -o samples.pdf

       Make PostScript samples for font.ttf and write output to file samples.ps.  Show only glyphs for characters with	codes  less  or  equal	to
       U+04FF but exclude U+0370-U+03FF:
	      fntsample -f font.ttf -s -o samples.ps -i -0x04FF -x 0x0370-0x03FF

       Make PDF samples for font.ttf and save output to file samples.pdf adding outlines to it:
	      fntsample -f font.ttf -o temp.pdf -l > outlines.txt
	      pdfoutline temp.pdf outlines.txt samples.pdf

AUTHOR

       Copyright (C) 2007 Eugeniy Meshcheryakov <eugen@debian.org>
       Homepage: <http://fntsample.sourceforge.net/>

SEE ALSO

       pdfoutline(1)

								    2010-10-14							      fntsample(1)
All times are GMT -4. The time now is 11:12 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy