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
columnno
. 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
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, ',')