1

my data is like:

org_id  org_emp_id  name    day1    day2    day3    day4    day5    day6    day7    day8    day9    day10   day11   day12   day13   day14   day15   day16   day17   day18   day19   day20   day21   day22   day23   day24   day25   day26   day27   day28   day29   day30
'176'   '1' 'New Org NO'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'P' 'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'
'176'   '1' 'New Org NO'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'P' 'NA'    'NA'    'NA'    'NA'
'176'   '1' 'New Org NO'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'P' 'NA'    'NA'    'NA'
'176'   '1' 'New Org NO'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'P' 'NA'    'NA'

I want result like::

org_id  org_emp_id  name    day1    day2    day3    day4    day5    day6    day7    day8    day9    day10   day11   day12   day13   day14   day15   day16   day17   day18   day19   day20   day21   day22   day23   day24   day25   day26   day27   day28   day29   day30
'176'   '1' 'New Org NO'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'NA'    'P' 'NA'    'NA'    'NA'    'NA'    'NA'    'P' 'P' 'P' 'P' 'NA'
Shadow
  • 33,525
  • 10
  • 51
  • 64
Anand jha
  • 31
  • 2
  • `SELECT DISTINCT ....your query...` – 1000111 Apr 07 '16 at 10:05
  • Possible duplicate of [How to get unique values from comma separated values field?](http://stackoverflow.com/questions/4537838/how-to-get-unique-values-from-comma-separated-values-field) – Hanky Panky Apr 07 '16 at 10:06
  • Possible duplicate of [MySQL copy/duplicate database](http://stackoverflow.com/questions/25794/mysql-copy-duplicate-database) – Shiv Singh Apr 07 '16 at 10:13
  • 1
    @ShivSingh This topic is definitely not a duplicate of what you indicated. That has nothing to do with this question. Pls retract your close vote. – Shadow Apr 07 '16 at 10:21
  • 1
    @HankyPanky I believe that the title of the question is misleading. If you check out the formatted source data and expected results table, then you can see that the question is not about removing duplicate records. You need to scroll to the far right of both tables. – Shadow Apr 07 '16 at 10:23
  • @Shadow that makes sense – Hanky Panky Apr 07 '16 at 10:26

2 Answers2

0

Use DISTINCT in Your query statement, ex: select DISTINCT Id, name ,age from your_table

which means DISTINCT Id it avoid the duplicate id present in your table

rajsekar
  • 79
  • 1
  • 8
0

This is not about removing duplicates, since none of the records in the first resultset is a duplicate of another one. You want to merge the 'P' values for all fields into a single record. You can use group by and max() function to achive the expected outcome:

select org_id,  org_emp_id,  name, max(day1) as day1, ... , max(day30) as day30
from yourtable
group by org_id,  org_emp_id,  name

You need to list all dayN fields in place of the ... in the above query. If for a dayN field all values are 'N/A', then max() will return 'N/A'. If there is a 'P' value, then max() will return the 'P', since letter P has higher character code, than N.

Shadow
  • 33,525
  • 10
  • 51
  • 64
  • If the above answer helped to resolve your issue, then pls mark it as accepted, so other viewers of this topic will know that the questions is answered. – Shadow Apr 07 '16 at 12:17