Sponsored Content
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
MGD77INFO(1gmt) 					       Generic Mapping Tools						   MGD77INFO(1gmt)

NAME
mgd77info - Get information about MGD77[+] files SYNOPSIS
mgd77info NGDC-ids [ -C[m|e] ] [ -E[m|e] ] [ -Iignore ] [ -Mf[item]|r|e|h ] [ -L[v] ] [ -V ] DESCRIPTION
mgd77info reads <legid>.[mgd77|nc] files and produces a single record of information about each cruise specified. The information includes beginning and end times, total track distances in km, longitude and latitude range, and the total number of geophysical observations. Optionally, choose instead to see the original MGD77 header meta-data section or its individual members. If you need to know which tracks are crossing through a given region and what kinds of geophysical observations are available, consider using the x2sys tools to set up a tracks index data base (see x2sys_init for more information). NGDC-ids Can be one or more of five kinds of specifiers: 1) 8-character NGDC IDs, e.g., 01010083, JA010010etc., etc. 2) 2-character <agency> codes which will return all cruises from each agency. 3) 4-character <agency><vessel> codes, which will return all cruises from those vessels. 4) =<list>, where <list> is a table with NGDC IDs, one per line. 5) If nothing is specified we return all cruises in the data base. (See mgd77info -L for agency and vessel codes). The ".mgd77" or ".nc" extensions will automatically be appended, if needed (use -I to ignore certain file types). Cruise files will be looked for first in the current directory and second in all directories listed in $MGD77_HOME/mgd77_paths.txt [If $MGD77_HOME is not set it will default to $GMT_SHAREDIR/mgd77]. OPTIONS
No space between the option flag and the associated arguments. -C List abbreviations for all columns present in the MGD77[+] files. Append m or e to limit the display to the MGD77 standard or MGD77+ extended set only. -E Give a one-line summary for each cruise listed. -M List the meta-data (header) and (if present) the MGD77+ history for each cruise. Append f for a formatted display. This will list individual parameters and their values, one entry per output line, in a format that can be searched using standard UNIX text tools. Alternatively, append the name of a particular parameter (you only need to give enough characters - starting at the beginning - to uniquely identify the item). Give - to display the list of all parameter names. You may also specify the number of a parameter. For the raw, punchcard-formatted MGD77 original header block, append r instead. For the MGD77+ E77 status, append e instead. Finally, for the MGD77+ history, append h instead. -I Ignore certain data file formats from consideration. Append a|c|t to ignore MGD77 ASCII, MGD77+ netCDF, or plain tab-separated ASCII table files, respectively. The option may be repeated to ignore more than one format. [Default ignores none]. -L No cruise information is listed. Instead, we just display a list of the GEODAS institution 2-character codes and their names. Optionally, append v to also display the vessels and their 4-character codes for each institution. The following is the list of institutions:(01) LAMONT (LDEO), (02) WOODS HOLE O.I., (03) NOAA, (04) US ARMY, (05) NEW ZEALAND, (06) US GEOL. SURVEY, (07) OREGON ST. UNIV, (08) U.HAWAII SOEST, (09) US NAVY, (10) UNIV OF TEXAS, (11) RICE UNIV., (12) CANADA, (13) UNIV OF CONN., (14) U.MIAMI (RSMAS), (15) SCRIPPS INST.OC, (16) CHINA, (17) U RHODE ISLAND, (18) DUKE UNIVERSITY, (19) UNITED KINGDOM, (20) U.WASHINGTON, (22) WESTERN GEO- PHY., (23) TEXAS A&M UNIV., (24) AUSTRALIA, (25) MONACO, (29) RUSSIA, (30) SPAIN, (35) NIMA, (58) NETHERLANDS, (60) MIN MGMT SVC, (63) ISRAEL, (67) FRANCE, (71) SOUTH AFRICA, (75) US COAST GUARD, (76) BRAZIL, (77) INT. GRAV. BUR, (83) GERMANY, (84) ORSTOM NEW CAL, (86) CUBA, (87) ARGENTINA, (88) US NSF, (89) INDIA, (90) PORTUGAL, (92) FINLAND, (93) CHILE, (J1) HYDR DEPT JAPAN, (J2) GEOL SRVY JAPAN, (J4) UNIV TOKYO, (J5) KOBE UNIV, (J7) UNIV OF RYUKYUS, (J8) J.O.D.C. JAPAN, (J9) CHIBA UNIV, (JA) INST.POLAR RES., (ZZ) INST NOT CODED. -V Selects verbose mode, which will send progress reports to stderr [Default runs "silently"]. EXAMPLES
To get one-line summary information about the cruises 01010047.mgd77 and 01010008.mgd77, try mgd77info 01010047 01010008 -E > listing.lis To see the original raw MGD77 header meta-data for cruise 01010047.mgd77, run mgd77info 01010047 -Mr To determine all the parameters related to Gravity during cruise 01010047.mgd77, run mgd77info 01010047 -Mf | grep Gravity To determine the Magnetic sampling rate used during cruise 01010047.mgd77, run mgd77info 01010047 -MfMagnetics_Sampling_Rate To see all the columns that the MGD77+ cruise 01010047.nc contains, run mgd77info 01010047 -C To see the E77 status of all MGD77+ cruises collected by the University of Hawaii, run mgd77info 08 -Ia -Me SEE ALSO
mgd77list(1), mgd77manage(1), mgd77path(1), mgd77track(1) x2sys_init(1) REFERENCES
Wessel, P., and W. H. F. Smith, 2011, The Generic Mapping Tools (GMT) version 4.5.7 Technical Reference & Cookbook, SOEST/NOAA. Wessel, P., and W. H. F. Smith, 1998, New, Improved Version of Generic Mapping Tools Released, EOS Trans., AGU, 79(47), p. 579. Wessel, P., and W. H. F. Smith, 1995, New Version of the Generic Mapping Tools Released, EOS Trans., AGU, 76(33), p. 329. Wessel, P., and W. H. F. Smith, 1995, New Version of the Generic Mapping Tools Released, http://www.agu.org/eos_elec/95154e.html, Copyright 1995 by the American Geophysical Union. Wessel, P., and W. H. F. Smith, 1991, Free Software Helps Map and Display Data, EOS Trans., AGU, 72(41), p. 441. The Marine Geophysical Data Exchange Format - "MGD77", see http://www.ngdc.noaa.gov/mgg/dat/geodas/docs/mgd77.txt GMT 4.5.7 15 Jul 2011 MGD77INFO(1gmt)
All times are GMT -4. The time now is 04:06 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy