热心人也帮我写一个SQL查询语句

Question

表内容大概如下：FieldA         FieldB         FieldC
-----------------------------------------
A001           1,2,3          0
A001           2,3            1
A002           1,2            0
A002           5              0
A002           3              0
A003           9,11           1
....           .....          .
----------------------------------------我要写个查询直接得出如下结果
FieldA         FieldB         FieldC
-----------------------------------------
A001           1,2,3,2,3      1
A002           1,2,5,3        0
A003           9,11           1
....           .....          .
----------------------------------------第一列是取Group，第二列是相同Group累加，第三列很明显，是取Bit型的最大值(true)大约想要的查询如下:
Select FieldA,   (????) as FieldB,  Max(fieldC) as fieldC
From MyTable1
Group by FieldA第二个字段真不知怎么直接用SQL查询语句写？？大伙帮忙，写个效率高点的，可能该表的数据量比较多。

Answer 1

create table f(fa varchar(30),fb varchar(30), fc bit)
insert into f
select 'a001','1,2,3',0 union
select 'a001','2,3',1 union
select 'a002','1,2',0 union
select 'a002','5',0 union
select 'a002','3',0 union
select 'a003','9,11',1 
go
create function f_str(@department varchar(20))
returns varchar(8000)
as
begin
    declare @ret varchar(8000)
    set @ret = ''
    select @ret = @ret+','+fb from f where fa = @department
    set @ret = stuff(@ret,1,1,'')
    return @ret 
end
goselect fa,dbo.f_str(fa) from f group by fa
drop function f_str
drop table f

Answer 2

--用函数
create function f(@id varchar(10))
returns varchar(8000)
as
begin 
declare @str varchar(8000)

set @str=''
select @str=@str+','+FidldB from 表名 where FieldA = @id
set @str=stuff(@str,1,1,'')
return @str
end
goSelect   FieldA,dbo.f(FieldA) FieldB,Max(fieldC) fieldC 
From   MyTable1 
Group   by   FieldA 

Answer 3

用函数呀
create   table   ta(FieldA   varchar(30),FieldB   varchar(30),   FieldC   int) 
insert   into   ta 
select   'a001','1,2,3',0   union 
select   'a001','2,3',1   union 
select   'a002','1,2',0   union 
select   'a002','5',0   union 
select   'a002','3',0   union 
select   'a003','9,11',1   
go 
create   function   f_str(@str   varchar(20)) 
returns   varchar(8000) 
as 
begin 
        declare   @ret   varchar(8000) 
        set   @ret   =   '' 
        select   @ret   =   @ret+','+FieldB   from   ta   where   FieldA   =   @str 
        set   @ret   =   stuff(@ret,1,1,'') 
        return   @ret   
end 
go select FieldA,dbo.f_str(FieldA) as FieldB,max(FieldC) as FieldC
from ta
group by FieldAdrop table ta
drop function f_str/*\
FieldA                         FieldB                                                                                                                                                                                                                                                           FieldC      
------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- 
a001                           1,2,3,2,3                                                                                                                                                                                                                                                        1
a002                           1,2,3,5                                                                                                                                                                                                                                                          0
a003                           9,11                                                                                                                                                                                                                                                             1（所影响的行数为 3 行）
*/

Answer 4

create table tb(FieldA varchar(10),FieldB varchar(10),FieldC varchar(10),)
insert into tb values('A001',                       '1,2,3',                     0 )
insert into tb values('A001',                       '2,3'  ,                     1 )
insert into tb values('A002',                       '1,2'  ,                     0 )
insert into tb values('A002',                       '5'    ,                     0 )
insert into tb values('A002',                       '3'    ,                     0 )
insert into tb values('A003',                       '9,11' ,                     1 )
go--创建一个合并的函数
create function f_hb(@fielda varchar(10))
returns varchar(8000)
as
begin
  declare @str varchar(8000)
  set @str = ''
  select @str = @str + ',' + cast(fieldb as varchar) from tb where fielda = @fielda 
  set @str = right(@str , len(@str) - 1)
  return(@str)
End
go--调用自定义函数得到结果：
select t2.* , t1.fieldc from
(select fielda , max(fieldc) fieldc from tb group by fielda) t1,
(select distinct fielda ,dbo.f_hb(fielda) as fieldb from tb) t2
where t2.fielda = t1.fieldadrop table tb
drop function f_hb/*
fielda     fieldb     fieldc     
---------- ---------- ---------- 
A001       1,2,3,2,3  1
A002       1,2,5,3    0
A003       9,11       1（所影响的行数为 3 行）
*/

Answer 5

bit类型不能用max 
改为int

Answer 6

create table tb(FieldA varchar(10),FieldB varchar(10),FieldC varchar(10),)
insert into tb values('A001',                       '1,2,3',                     0 )
insert into tb values('A001',                       '2,3'  ,                     1 )
insert into tb values('A002',                       '1,2'  ,                     0 )
insert into tb values('A002',                       '5'    ,                     0 )
insert into tb values('A002',                       '3'    ,                     0 )
insert into tb values('A003',                       '9,11' ,                     1 )
go--创建一个合并的函数
create function f_hb(@fielda varchar(10))
returns varchar(8000)
as
begin
  declare @str varchar(8000)
  set @str = ''
  select @str = @str + ',' + cast(fieldb as varchar) from tb where fielda = @fielda 
  set @str = right(@str , len(@str) - 1)
  return(@str)
End
go--调用自定义函数得到结果：
select distinct tb.fielda ,dbo.f_hb(tb.fielda) as fieldb , fieldc = max(t.fieldc) 
from tb , tb t where tb.fielda = t.fielda
group by tb.fielda , tb.fieldbdrop table tb
drop function f_hb/*
fielda     fieldb     fieldc     
---------- ---------- ---------- 
A001       1,2,3,2,3  1
A002       1,2,5,3    0
A003       9,11       1（所影响的行数为 3 行）
*/