请教一个不定行转列的SQL查询语句

Question

原始表:COL1 COL2 COL3
A     1   101
A     2   201
A     3   301
A     3   302
A     4   401
A     4   402
A     4   403
B     1   101
B     2   201
B     3   301
B     4   401转换后的结果应为:
COL1  COL2
A     101,201,301或302,401或402或403
B     101,201,301,401说明:COL3的值是我自己随便输入的值,这里只是为了方便；COL2的值只能是1-4；但COL1、COL2、COL3的同一行的组合值肯定唯一；
要求是：将COL1中相同的值组合成一条数据，并且如果COL1中的值相同且COL2值也同，则要用“或”字将它们连接起来；总体的排列顺序是COL1，COL2，COL3。
谢谢，如果能用SQL来直接解决最好，闲分不够我再另外开贴谢谢。

Answer 1

--参考---------------------------------------------------------------------
--生成测试数据
create table 表(部门 int,人员 varchar(20))
insert into 表 select 1,'张三'
insert into 表 select 1,'李四'
insert into 表 select 1,'王五'
insert into 表 select 2,'赵六'
insert into 表 select 2,'邓七'
insert into 表 select 2,'刘八'
go--创建用户定义函数
create function f_str(@department int)
returns varchar(8000)
as
begin
    declare @ret varchar(8000)
    set @ret = ''
    select @ret = @ret+','+人员 from 表 where 部门 = @department
    set @ret = stuff(@ret,1,1,'')
    return @ret 
end
go
--执行
select 部门,人员=dbo.f_str(部门) from 表 group by 部门 order by 部门
go--输出结果
/*
部门  人员
----  --------------
1     张三,李四,王五
2     赵六,邓七,刘八
*/
--删除测试数据
drop function f_str
drop table 表
go

Answer 2

谢谢aw511(点点星灯) 
不知道还没有其它办法，比如直接的SQL语句或者分解的SQL语句最好针对我的例子

Answer 3

是不是每一個COL1都固定會有COL3為1,2,3,4的數據？？

Answer 4

Create Table TEST
(COL1 Char(1),
 COL2 Int,
 COL3 Int)
Insert TEST Select 'A',     1,   101
Union All Select 'A',     2,   201
Union All Select 'A',     3,   301
Union All Select 'A',     3,   302
Union All Select 'A',     4,   401
Union All Select 'A',     4,   402
Union All Select 'A',     4,   403
Union All Select 'B',     1,   101
Union All Select 'B',     2,   201
Union All Select 'B',     3,   301
Union All Select 'B',     4,   401
GO
Create Function GetCOL3(@COL1 Char(1))
Returns Nvarchar(4000)
As
Begin
Declare @S Nvarchar(4000),@S1 Nvarchar(4000),@S2 Nvarchar(4000),@S3 Nvarchar(4000),@S4 Nvarchar(4000)
Select @S='',@S1='',@S2='',@S3='',@S4=''
Select @S1=@S1+N'或'+Rtrim(COL3)  From TEST  Where COL1=@COL1 And COL2=1
Select @S2=@S2+N'或'+Rtrim(COL3)  From TEST  Where COL1=@COL1 And COL2=2
Select @S3=@S3+N'或'+Rtrim(COL3)  From TEST  Where COL1=@COL1 And COL2=3
Select @S4=@S4+N'或'+Rtrim(COL3)  From TEST  Where COL1=@COL1 And COL2=4
Select @S=Stuff(@S1,1,1,'')+','+Stuff(@S2,1,1,'')+','+Stuff(@S3,1,1,'')+','+Stuff(@S4,1,1,'')
Return @S
End
GO
Select 
COL1,
dbo.GetCOL3(COL1) As COL2
From TEST
Group By COL1
GO
Drop Table TEST
Drop Function GetCOL3
GO
/*
COL1 COL2
A 101,201,301或302,401或402或403
B 101,201,301,401
*/

Answer 5

用這個語句建立函數Create Function GetCOL3(@COL1 Char(1))
Returns Nvarchar(4000)
As
Begin
Declare @S Nvarchar(4000),@S1 Nvarchar(4000),@S2 Nvarchar(4000),@S3 Nvarchar(4000),@S4 Nvarchar(4000)
Select @S='',@S1='',@S2='',@S3='',@S4=''
Select @S1=@S1+N'或'+Rtrim(COL3)  From TEST  Where COL1=@COL1 And COL2=1
Select @S2=@S2+N'或'+Rtrim(COL3)  From TEST  Where COL1=@COL1 And COL2=2
Select @S3=@S3+N'或'+Rtrim(COL3)  From TEST  Where COL1=@COL1 And COL2=3
Select @S4=@S4+N'或'+Rtrim(COL3)  From TEST  Where COL1=@COL1 And COL2=4
Select @S=Stuff(@S1,1,1,'')+','+Stuff(@S2,1,1,'')+','+Stuff(@S3,1,1,'')+','+Stuff(@S4,1,1,'')
Return @S
End
之後查詢時，只需執行以下語句即可
Select 
COL1,
dbo.GetCOL3(COL1) As COL2
From TEST
Group By COL1

Answer 6

谢谢
paoluo(一天到晚游泳的鱼) ：我测试了，如果对于每一个COL1的值都有COL2的1、2、3、4的值的时候，完全正确，正是我需要的那种类型。但有个问题是，如果上述条件不满足，即COL2有可能只有2和3，也可能只有1和2和3的情况，就不正确了，最后的值是NULL。
还请您帮忙修改一下，谢谢。

Answer 7

OK，修改下Create Table TEST
(COL1 Char(1),
 COL2 Int,
 COL3 Int)
Insert TEST Select 'A',     1,   101
Union All Select 'A',     2,   201
Union All Select 'A',     3,   301
Union All Select 'A',     3,   302
Union All Select 'A',     4,   401
Union All Select 'A',     4,   402
Union All Select 'A',     4,   403
Union All Select 'B',     1,   101
Union All Select 'B',     2,   201
Union All Select 'B',     3,   301
Union All Select 'B',     4,   401
Union All Select 'C',     1,   101
Union All Select 'C',     2,   201
Union All Select 'C',     3,   301
Union All Select 'C',     3,   302
Union All Select 'D',     3,   301
Union All Select 'D',     4,   401
Union All Select 'D',     4,   402
GO
Create Function GetCOL3(@COL1 Char(1))
Returns Nvarchar(4000)
As
Begin
Declare @S Nvarchar(4000),@S1 Nvarchar(4000),@S2 Nvarchar(4000),@S3 Nvarchar(4000),@S4 Nvarchar(4000)
Select @S='',@S1='',@S2='',@S3='',@S4=''
Select @S1=@S1+N'或'+Rtrim(COL3)  From TEST  Where COL1=@COL1 And COL2=1
Select @S2=@S2+N'或'+Rtrim(COL3)  From TEST  Where COL1=@COL1 And COL2=2
Select @S3=@S3+N'或'+Rtrim(COL3)  From TEST  Where COL1=@COL1 And COL2=3
Select @S4=@S4+N'或'+Rtrim(COL3)  From TEST  Where COL1=@COL1 And COL2=4
Select @S=IsNull(Stuff(@S1,1,1,'')+',','')+IsNull(Stuff(@S2,1,1,'')+',','')+IsNull(Stuff(@S3,1,1,'')+',','')+IsNull(Stuff(@S4,1,1,''),'')
If Right(@S,1)=','
Select @S=Left(@S,Len(@S)-1)
Return @S
End
GO
Select 
COL1,
dbo.GetCOL3(COL1) As COL2
From TEST
Group By COL1
GO
Drop Table TEST
Drop Function GetCOL3
GO
/*
COL1 COL2
A 101,201,301或302,401或402或403
B 101,201,301,401
C 101,201,301或302
D 301,401或402
*/

Answer 8

非常感谢paoluo(一天到晚游泳的鱼)我结题了。