表 tbltest数据如下:列A 列B 列B
1 1 A
1 1 B
1 1 C
1 2 F
1 2 G
2 1 E
2 1 F
2 2 FSQL文结果列A 列B 列B
1 1 A,B,C
1 2 F,G
2 1 E,F
2 2 F
1 1 A
1 1 B
1 1 C
1 2 F
1 2 G
2 1 E
2 1 F
2 2 FSQL文结果列A 列B 列B
1 1 A,B,C
1 2 F,G
2 1 E,F
2 2 F
解决方案 »
- sql如何将一条内容包含空格的记录拆分成多条记录
- 怎么取表的备注信息?
- 结果集表现形式转换?
- 请教各位数据库高手:我表中有这样一列aa(int型)存取数据为0,1我要在客户端软件中这样显示0显示公历,1显示农历SQL怎样写?
- 怎么得出某个字段某特定字符(串)出现的次数?
- 有没有将sql server 2000的存储过程移植到oracle上的工具? 在线等待,500分.
- 我的操作系统是XP 为何装不上SQL2000???
- 關於MS SQL Server碎片整理
- 用bc5调用sql server7库写程序的一个奇怪的问题。
- 如何在SQL SERVER中定期创建一个库?(用SQL语句实现,或存储过程),非常感谢!!!
- 如何查询出所有记录?
- 急,在线等,重命名默认实例
go
insert into tbltest
select 1,1,'A' union all
select 1,1,'B' union all
select 1,1,'C' union all
select 1,2,'F' union all
select 1,2,'G' union all
select 2,1,'E' union all
select 2,1,'F' union all
select 2,2,'F'
go
--写一个聚合函数:
create function dbo.fn_Merge(@F1 int,@F2 int)
returns varchar(8000)
as
begin
declare @r varchar(8000)
set @r=''
select @r=@r+','+列C from tbltest where 列A=@F1 and 列B=@F2
return stuff(@r,1,1,'')
end
go-- 调用函数
select 列A,列B, dbo.fn_Merge(列A,列B) as 列C from tbltest group by 列A,列B
go
drop table tbltest
drop function fn_Merge
insert ta
select 1, 1, 'A'union all
select 1, 1, 'B'union all
select 1, 1, 'C'union all
select 1, 2, 'F'union all
select 1, 2, 'G'union all
select 2, 1, 'E'union all
select 2, 1, 'F'union all
select 2, 2, 'F'
create function ta_fun(@A int,@B int)
returns varchar(1000)
as
begin
declare @sql varchar(1000)
set @sql=''
select @sql=@sql+','+列C from ta where 列A=@A and 列B=@B
--print @sql
return stuff(@sql,1,1,'')
endselect distinct 列A,列B,显示=dbo.ta_fun(列A,列B) from ta
列A 列B 显示
----------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 1 A,B,C
1 2 F,G
2 1 E,F
2 2 F(所影响的行数为 4 行)
INSERT INTO tb
SELECT 1, 1, 'A'
UNION ALL SELECT 1, 1, 'B'
UNION ALL SELECT 1, 1, 'C'
UNION ALL SELECT 1, 2, 'F'
UNION ALL SELECT 1, 2, 'G'
UNION ALL SELECT 2, 1, 'E'
UNION ALL SELECT 2, 1, 'F'
UNION ALL SELECT 2, 2, 'F'
GOCREATE FUNCTION GetStr(@A INT, @B INT)
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @RE VARCHAR(1000)
SET @RE = ''
SELECT @RE = @RE + 列C + ',' FROM tb WHERE 列A = @A AND 列B = @B
RETURN LEFT(@RE, LEN(@RE) - 1)
END
GOSELECT T.列A, T.列B, dbo.GetStr(T.列A, T.列B)
FROM (SELECT DISTINCT 列A, 列B FROM tb)TDROP FUNCTION GetStr
DROP TABLE tb
--如下用于几列合并一列方法1比方法2效率高
declare @tb table(列A int,列B int,列C varchar(50),con int identity(1,1))
insert @tb
select * from tabegin tran
while exists(select 1 from @tb)
begin
update a
set a.列C=a.列C+','+b.列C
from ta a ,@tb b
where a.列A=b.列A and a.列B=b.列B and
not exists(select * from @tb where 列A=b.列A and 列B=b.列B and con<b.con )delete b
from @tb b where not exists(select 1 from @tb where 列A=b.列A and 列B=b.列B and con<b.con)
endselect distinct 列A,列B, [列B显示]=stuff(列C,1,charindex(',',列C),'') from ta
所影响的行数为 1 行)列A 列B 列B显示
----------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 1 A,B,C
1 2 F,G
2 1 E,F
2 2 F(所影响的行数为 4 行)