一个A表:
A B
20GP JJ100
40GP JJ100
20GP JJ200按B分组怎么得出如下:
A B
20GP,40GP JJ100
20GP JJ200谢谢!~
----------------
http://community.csdn.net/Expert/topic/5142/5142165.xml?temp=6.780642E-02
这个贴相同,原先没考虑到SQL7;在SQL2000可以,在SQL7下不知道怎么解决,没有用户定义的函数,
知道的请帮忙一下,谢谢!~
A B
20GP JJ100
40GP JJ100
20GP JJ200按B分组怎么得出如下:
A B
20GP,40GP JJ100
20GP JJ200谢谢!~
----------------
http://community.csdn.net/Expert/topic/5142/5142165.xml?temp=6.780642E-02
这个贴相同,原先没考虑到SQL7;在SQL2000可以,在SQL7下不知道怎么解决,没有用户定义的函数,
知道的请帮忙一下,谢谢!~
(
AA nvarchar(255),
BB nvarchar(255)
)
declare @A nvarchar(255)
declare @B nvarchar(255)declare cursor_1 cursor for
select A,B from 表名
open cursor_1fetch next from cursor_1 into @A,@B
while @@fetch_status = 0
begin
if not exists(select * from ttt where BB=@B)
begin
insert into ttt (AA,BB) values (@A,@B)
end
else
begin
update ttt set AA=AA+@A where BB=@B
end
fetch next from cursor_1 into @A,@B
end
close cursor_1
deallocate cursor_1
select AA,BB from ttt
drop table ttt
(
AA nvarchar(255),
BB nvarchar(255)
)
declare @A nvarchar(255)
declare @B nvarchar(255)declare cursor_1 cursor for
select A,B from 表名
open cursor_1fetch next from cursor_1 into @A,@B
while @@fetch_status = 0
begin
if not exists(select * from ttt where BB=@B)
begin
insert into ttt (AA,BB) values (@A,@B)
end
else
begin
update ttt set AA=AA+','+@A where BB=@B
end
fetch next from cursor_1 into @A,@B
end
close cursor_1
deallocate cursor_1
select AA,BB from ttt
drop table ttt前面忘记加逗号了
INSERT INTO tb
SELECT '20GP', 'JJ100'
UNION ALL SELECT '40GP', 'JJ100'
UNION ALL SELECT '20GP', 'JJ200'SELECT A, B INTO #t FROM tb
ALTER TABLE #t ALTER COLUMN A VARCHAR(8000)DECLARE @B VARCHAR(10)
DECLARE @A VARCHAR(100)
SELECT @A = '', @B = ''
UPDATE #t
SET @A = (CASE WHEN @B = B THEN @A + A + ',' ELSE A + ',' END),
A = LEFT(@A, LEN(@A) - 1),
@B = B
SELECT A, B FROM #T T WHERE NOT EXISTS(SELECT 1 FROM #T WHERE B = T.B AND LEN(A) > LEN(T.A))DROP TABLE #t
DROP TABLE tb
a b
1 1
1 2
1 3
2 1
2 2
3 1
如何转换成如下结果:
a b
1 1,2,3
2 1,2
3 1 创建一个合并的函数 create function f_rowtocol(@a int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ''
select @str = @str + ',' + cast(b as varchar) from rowtocol where a = @a
set @str = right(@str , len(@str) - 1)
return(@str)
End
go
调用自定义函数得到结果:
select distinct a ,dbo.f_rowtocol(a) from rowtocol
drop table t
gocreate table t(
a varchar(10),
b varchar(10)
)insert into t(a,b) values('20GP','JJ100')
insert into t(a,b) values('40GP','JJ100')
insert into t(a,b) values('20GP','JJ200')
go--创建一个合并的函数
if object_id('pubs..f_hb') is not null
drop function f_hb
gocreate function f_hb(@b varchar(10))
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ''
select @str = @str + ',' + cast(a as varchar) from t where b = @b
set @str = right(@str , len(@str) - 1)
return(@str)
End
go
--调用自定义函数得到结果:
select distinct dbo.f_hb(b) as a , b from tdrop table t
drop function f_hb--结果
a b
--------- -----
20GP JJ200
20GP,40GP JJ100(所影响的行数为 2 行)