现有源表如下
***************************
ColumnA ColumnB
Group1 Key1
Group1 Key2
Group1 Key3
Group2 Key2
Group2 Key4
Group2 Key5
Group3 Key1
Group3 Key2
Group3 Key3
...
***************************
注:Key的数量位置
一个Group中有多个Key, 一个Key也能属于多个Group,一个多对多关系的表.最后的要求是生成一张表,表明Group和Key的关系,如果多个Group下的Key相同,那么在表中只要求列出一次就可以了.(比如上表中的Group1和Group3)
***************************
ColumnC ColumnD
1 Key1
1 Key2
1 Key3
2 Key2
2 Key4
2 Key5
***************************请高手解答!
***************************
ColumnA ColumnB
Group1 Key1
Group1 Key2
Group1 Key3
Group2 Key2
Group2 Key4
Group2 Key5
Group3 Key1
Group3 Key2
Group3 Key3
...
***************************
注:Key的数量位置
一个Group中有多个Key, 一个Key也能属于多个Group,一个多对多关系的表.最后的要求是生成一张表,表明Group和Key的关系,如果多个Group下的Key相同,那么在表中只要求列出一次就可以了.(比如上表中的Group1和Group3)
***************************
ColumnC ColumnD
1 Key1
1 Key2
1 Key3
2 Key2
2 Key4
2 Key5
***************************请高手解答!
returns varchar(50)
AS
begin
declare @str varchar(50)
set @str=''
select @str=@str+','+c2 from T where c1=@c1 order by c2
set @str=stuff(@str,1,1,'')
return @str
endGOcreate table t(c1 varchar(10),c2 varchar(10))insert into t
select 'g1','k1' union all
select 'g1','k2' union all
select 'g1','k3' union all
select 'g2','k2' union all
select 'g2','k4' union all
select 'g2','k5' union all
select 'g3','k1' union all
select 'g3','k2' union all
select 'g3','k3' union all
select 'g4','k1' union all
select 'g5','k3' union all
select 'g5','k1' union all
select 'g5','k2' select right(c1,1) as c3,c2 from t where
c1 in (select min(c1)
from
(select c1,dbo.fn_test(c1) as c2 from t group by c1) A
group by c2)
drop function fn_test
drop table t
/*
c3 c2
---- ----------
1 k1
1 k2
1 k3
2 k2
2 k4
2 k5
4 k1
*/
4 K1这行有点问题,应该是3 .
可能我的问题有点误导,源表中的Group1, Group2只是我方便大家理解写的,但是实际中不是这样有规律的,可能是一组无规则的数字,比如Group1->41, Group2->24, 这样就不能用right()函数来生成C3这行了,有甚么其他办法吗?
from t where
c1 in (select min(c1)
from
(select c1,dbo.fn_test(c1) as c2 from t group by c1) A
group by c2)select c3=(select count(distinct c1) from #t where #t.c1<=a.c1),
c2
from #t a
c3 c2
----------- ----------
1 k1
1 k2
1 k3
2 k2
2 k4
2 k5
3 k1