Avoid duplicate values in a column

Question

My Table table1 looks like this:

 no    name

 1      C_GT_2013-2014,C_GT_AcMaster,C_GT_Master
 1      C_GT_2014-2015,C_GT_AcMaster,C_GT_Master
 2      C_TGYY_2013-2014,G_New_AcMaster,G_New_Master
 2      C_TGYY_2014-2015,G_New_AcMaster,G_New_Master
 3      C_TGYN_2013-2014,G_ACYMAN_AcMaster,C_TGYN_Master
 3      C_TGYN_2014-2015,G_ACYMAN_AcMaster,C_TGYN_Master
 4      C_TGNY_2013-2014,C_TGNY_AcMaster,G_ACNMAY_Master
 4      C_TGNY_2014-2015,C_TGNY_AcMaster,G_ACNMAY_Master
 5      C_GYY_2013-2014,G_New_AcMaster,G_New_Master
 6      C_DD_2013-2014,C_DD_AcMaster,G_ACNMAY_Master
 7      C_YN_2013-2014,G_ACYMAN_AcMaster,C_YN_Master

I want to retrieve the value without duplicate no and split the name using "," so I tried like this:

select (string_to_array(name,','))[3] as master
      ,(string_to_array(name,','))[2] as acmaster
      ,string_agg((string_to_array(name,','))[1],',') as trans
      ,no
 from table t
 where no in (2,3,4,6,7)
 group by (string_to_array(schemaname,','))[3]
        , (string_to_array(schemaname,','))[2]
        , no
 order by no

But it return no duplicate. I want to get the value

No duplicate in no column
Name have same no. It merage using ',' like this (no 1 name with split(',') C_GT_2013-2014,C_GT_2014-2015)

I am using Postgresql 9.3.

My result would be:

I want to split Name into three columns which word have master its comes to Master column, Which word have its Comes to AcMaster, Others Comes to Trans With out No duplicate

 Master            AcMaster                  Trans                                   No

 G_New_Master     G_New_AcMaster             C_TGYY_2013-2014,C_TGYY_2014-2015        1
 C_TGYN_Master,   G_ACYMAN_AcMaster          C_TGYN_2014-2015,C_TGYN_2013-2014,       2
 C_YN_Master                                             C_YN_2013-2014    

 G_ACNMAY_Master  C_DD_AcMaster,              C_DD_2013-2014                          3
                  C_TGNY_AcMaster             C_TGNY_2013-2014,C_TGNY_2014-2015

Please show the desired results for the data you have posted. — Gordon Linoff
– Gordon Linoff, Commented Feb 4, 2014 at 14:17
. . That didn't help. What is the relationship between the columns and values in your results and the columns and values in the original table? — Gordon Linoff
– Gordon Linoff, Commented Feb 4, 2014 at 14:30
@GordonLinoff I want to split Name into three columns which word have master its comes to Master column, Which word have its Comes to AcMaster, Others Comes to Trans With out No duplicate — Sathish
– Sathish, Commented Feb 4, 2014 at 14:39
I think you did not format correctly your desired output. i did not understand what do you want too! — Houari
– Houari, Commented Feb 4, 2014 at 16:42
Your ultimate goal should be to normalize the database schema and sidestep these (and many other) kinds of problems altogether. By putting several values in the same field, you are violating the principle of atomicity and therefore the 1NF. — Branko Dimitrijevic
– Branko Dimitrijevic, Commented Feb 4, 2014 at 18:14

Rida BENHAMMANE · Accepted Answer · 2014-02-05 09:55:39Z

1

Here is your query :

select no,
string_agg(case when name like '%\_Master' escape '\' then name end, ','),
string_agg(case when name like '%\_AcMaster' escape '\' then name end, ','),
string_agg(case when (name not like '%\_Master' and name not like '%\_AcMaster' escape '\') then name end, ',')
from
(select no, (string_to_array(name,','))[1] as name
from   table1
union
select no, (string_to_array(name,','))[2] as name
from   table1
union
select no, (string_to_array(name,','))[3] as name
from   table1) t
where no in (2,3,4,6,7)
group by no
order by no

This query result is :

no  Master              AcMaster        Trans

2   G_New_Master        G_New_AcMaster      C_TGYY_2014-2015,C_TGYY_2013-2014
3   C_TGYN_Master       G_ACYMAN_AcMaster   C_TGYN_2014-2015,C_TGYN_2013-2014
4   G_ACNMAY_Master     C_TGNY_AcMaster     C_TGNY_2013-2014,C_TGNY_2014-2015
6   G_ACNMAY_Master     C_DD_AcMaster       C_DD_2013-2014
7   C_YN_Master         G_ACYMAN_AcMaster   C_YN_2013-2014

And now some explanations :

1- I flatten the your table :

(select no, (string_to_array(name,','))[1] as name
from   table1
union
select no, (string_to_array(name,','))[2] as name
from   table1
union
select no, (string_to_array(name,','))[3] as name
from   table1)

so I can have results like this :

4   G_ACNMAY_Master
1   C_GT_Master
3   G_ACYMAN_AcMaster
1   C_GT_2013-2014
2   G_New_Master
2   G_New_AcMaster
1   C_GT_2014-2015
...

2- I select the 3 required columns :

case when name like '%\_Master' escape '\' then name end -- Master
case when name like '%\_AcMaster' escape '\' then name end -- AcMaster
case when (name not like '%\_Master' and name not like '%\_AcMaster' escape '\') then name end -- Trans

getting the following result :

2   G_New_Master
2                                       C_TGYY_2014-2015
2                   G_New_AcMaster
2                                       C_TGYY_2013-2014
3                                       C_TGYN_2014-2015
...

3- After that I aggregate the result using the string_agg function grouping by no :

string_agg(case when name like '%\_Master' escape '\' then name end, ',')

edited Feb 5, 2014 at 9:55

answered Feb 5, 2014 at 0:24

Rida BENHAMMANE

4,1291 gold badge16 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Craig Ringer Over a year ago

That is the most incredibly convoluted way of manipulating data I've ever seen.

Rida BENHAMMANE Over a year ago

@CraigRinger Sure it's convoluted but I haven't found another way to do this :)

Rida BENHAMMANE Over a year ago

@CraigRinger I have updated my answer, do you still found it convoluted :)

Collectives™ on Stack Overflow

Avoid duplicate values in a column

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related