简单有关问题,送分了

简单问题,送分了
我有个表有以下数据:
a b c d
a,a tt,tt cc,dd ee,ee
b,b tt,cc cc,cc ff,gg
c,c aa,bb tt,tt aa,aa


数据是逗号分开的,我想把逗号左边和右边相同的数据只显示一次,并且把逗号去掉,左边和右边不相同的数据就保留原样显示,请问高手怎么实现?

[解决办法]

SQL code

create table tb(   a varchar(5),   b varchar(10),   c varchar(10),   d varchar(10))insert into tbselect 'a,a','tt,tt','cc,dd','ee,ee' union allselect 'b,b','tt,cc','cc,cc','ff,gg' union allselect 'c,c','aa,bb','tt,tt','aa,aa'select case when LEFT(a,CHARINDEX(',',a)-1)=right(a,len(a)-CHARINDEX(',',a)) then LEFT(a,CHARINDEX(',',a)-1) else a end as a, case when LEFT(b,CHARINDEX(',',b)-1)=right(b,len(b)-CHARINDEX(',',b)) then LEFT(b,CHARINDEX(',',b)-1) else b end as b, case when LEFT(c,CHARINDEX(',',c)-1)=right(c,len(c)-CHARINDEX(',',a)) then LEFT(c,CHARINDEX(',',c)-1) else c end as c, case when LEFT(d,CHARINDEX(',',d)-1)=right(d,len(d)-CHARINDEX(',',d)) then LEFT(d,CHARINDEX(',',d)-1) else d end as dfrom tb---------------a    b    c    da    tt    cc,dd    eeb    tt,cc    cc,cc    ff,ggc    aa,bb    tt,tt    aa
[解决办法]
Assembly code
create table #temp(    a varchar(100),    b varchar(100),    c varchar(100),    d varchar(100))insert into #tempselect 'a,a', 'tt,tt', 'cc,dd', 'ee,ee' union allselect 'b,b', 'tt,cc', 'cc,cc', 'ff,gg' union allselect 'c,c', 'aa,bb', 'tt,tt', 'aa,aa' select * from #tempSELECT     a = CASE WHEN (SUBSTRING(a, 1, CHARINDEX(',', a)-1)) = (SUBSTRING(a, CHARINDEX(',', a)+1, LEN(a)-CHARINDEX(',', a))) THEN (SUBSTRING(a, 1, CHARINDEX(',', a)-1)) ELSE a END,    b = CASE WHEN (SUBSTRING(b, 1, CHARINDEX(',', b)-1)) = (SUBSTRING(b, CHARINDEX(',', b)+1, LEN(b)-CHARINDEX(',', b))) THEN (SUBSTRING(b, 1, CHARINDEX(',', b)-1)) ELSE b END,    c = CASE WHEN (SUBSTRING(c, 1, CHARINDEX(',', c)-1)) = (SUBSTRING(c, CHARINDEX(',', c)+1, LEN(c)-CHARINDEX(',', c))) THEN (SUBSTRING(c, 1, CHARINDEX(',', c)-1)) ELSE c END,    d = CASE WHEN (SUBSTRING(d, 1, CHARINDEX(',', d)-1)) = (SUBSTRING(d, CHARINDEX(',', d)+1, LEN(d)-CHARINDEX(',', d))) THEN (SUBSTRING(d, 1, CHARINDEX(',', d)-1)) ELSE d ENDFROM #temp
[解决办法]
更新：
Assembly code
insert into #tempselect 'a', 'tt,tt', 'cc,dd', 'ee,ee' union allselect 'b', 'tt,cc', 'cc,cc', 'ff,gg' union allselect 'c', 'aa,bb', 'tt,tt', 'aa,aa' select * from #tempSELECT     a = CASE WHEN (LEN(a)%2 = 1 AND (CHARINDEX(',', a) = (LEN(a)+1)/2) AND LEFT(a, (LEN(a)-1)/2) = RIGHT(a, (LEN(a)-1)/2)) THEN LEFT(a, (LEN(a)-1)/2) ELSE a END,    b = CASE WHEN (LEN(b)%2 = 1 AND (CHARINDEX(',', b) = (LEN(b)+1)/2) AND LEFT(b, (LEN(b)-1)/2) = RIGHT(b, (LEN(b)-1)/2)) THEN LEFT(b, (LEN(b)-1)/2) ELSE b END,    c = CASE WHEN (LEN(c)%2 = 1 AND (CHARINDEX(',', c) = (LEN(c)+1)/2) AND LEFT(c, (LEN(c)-1)/2) = RIGHT(c, (LEN(c)-1)/2)) THEN LEFT(c, (LEN(c)-1)/2) ELSE c END,    d = CASE WHEN (LEN(d)%2 = 1 AND (CHARINDEX(',', d) = (LEN(d)+1)/2) AND LEFT(d, (LEN(d)-1)/2) = RIGHT(d, (LEN(d)-1)/2)) THEN LEFT(d, (LEN(d)-1)/2) ELSE d ENDFROM #temp
[解决办法]
用函数吧
SQL code
if object_id('[tb]') is not null drop table [tb]gocreate table [tb]([a] varchar(5),[b] varchar(8),[c] varchar(8),[d] varchar(8))insert [tb]select 'a,a','tt,tt','cc,dd','ee,ee' union allselect 'b,b','tt,cc','cc,cc','ff,gg' union allselect 'c','d','a','a' union allselect 'c,c','aa,bb','tt,tt','aa,aa' union allselect 'c','a','d','b' union allselect 'c,c,c','aa,bb,cc','tt,tt,tt','aa,aa,aa'gocreate function f_test(@s varchar(100))returns varchar(200)asbegin  declare @rel varchar(200);  declare @t table(a varchar(100));  set @s=@s+','  while charindex(',',@s)>0  begin    insert into @t select left(@s,charindex(',',@s)-1);    set @s=stuff(@s,1,charindex(',',@s),'');  end  select @rel=isnull(@rel+',','')+a from (select distinct a from @t) t  return @relendgoselect   dbo.f_test(a) as a,  dbo.f_test(b) as b,  dbo.f_test(c) as c,  dbo.f_test(d) as d from  tb/**a    tt    cc,dd    eeb    cc,tt    cc    ff,ggc    d    a    ac    aa,bb    tt    aac    a    d    bc    aa,bb,cc    tt    aa**/drop function f_testdrop table tb