參考﹕--建表
create table tb (A varchar(10),B varchar(1000))
Insert into tb
select '111','AA0000,AA0001,AA0002,'
union all select '111','BB0000,BB0001,BB0002,'
union all select '222','AA0120,AA0102,AA1453,'
union all select '222','BB1212,BB3423,BB2123,'select * from tb--函數
create function dbo.fn_b(@a varchar(10))
returns varchar(1000)
as
begin
declare @s varchar(1000)
set @s=''
select @s=@s+[B] from tb where A=@a
return (@s)
end--刪除
drop table tb
drop function dbo.fn_b--結果
select A,B=dbo.fn_b(A) from tb group by A
A B
-------------------------------------------
111 AA0000,AA0001,AA0002,BB0000,BB0001,BB0002,
222 AA0120,AA0102,AA1453,BB1212,BB3423,BB2123,
create table tb (A varchar(10),B varchar(1000))
Insert into tb
select '111','AA0000,AA0001,AA0002,'
union all select '111','BB0000,BB0001,BB0002,'
union all select '222','AA0120,AA0102,AA1453,'
union all select '222','BB1212,BB3423,BB2123,'select * from tb--函數
create function dbo.fn_b(@a varchar(10))
returns varchar(1000)
as
begin
declare @s varchar(1000)
set @s=''
select @s=@s+[B] from tb where A=@a
return (@s)
end--刪除
drop table tb
drop function dbo.fn_b--結果
select A,B=dbo.fn_b(A) from tb group by A
A B
-------------------------------------------
111 AA0000,AA0001,AA0002,BB0000,BB0001,BB0002,
222 AA0120,AA0102,AA1453,BB1212,BB3423,BB2123,
解决方案 »
- 问题:数据从EXCEL导入到SQL标中发生变化?如何处理----使得表中的内容与EXCEL中的完全一致.
- 新手 傻问题 请大家给点建议
- 一表有两个外键,为啥不能都设为级联删除呢?
- 如何通过程序把数据表建立语句response.write出来?
- File和Filegroup的问题
- 内、外网数据表复制,求实现方案
- 关于存储过程性能的疑问
- 下列语句是否能指定列:EXEC master..xp_cmdshell 'bcp 库名.dbo.表名out c:\Temp.xls -c -q -S"servername" -U"sa" -P""'
- 求SQL语句最佳方法!
- @@@@@如何使用SQL脚本远程其它机子上的SQLServer?@@@@@
- 修改存储过程,帮帮我呀!
- 統計查詢遇到的問題!
insert test(id,Pid)
select 1,'01'
union all
select 1,'02'
union all
select 2,'02'
union all
select 2,'03'
union all
select 2,'02'
union all
select 2,'05'select * from test
id Pid
----------------
1 01
1 02
2 02
2 03
2 02
2 05create function fmerg(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+','+cast(pid as varchar) from test where id=@id
set @str=right(@str,len(@str)-1)
return(@str)
End
goselect distinct id,dbo.fmerg(id) Pid from testid Pid
--------------
1 01,02
2 02,03,02,05
--刪除
drop table test
drop function dbo.fmerg
Insert into ta
select '1','aaa'
union all select '2','bbb'
union all select '3','ccc'create table tb (id int, value varchar(20))
Insert into tb
select '1','aaa,bbb,'
union all select '2','bbb,ccc,'
union all select '3','aaa,bbb,ccc,'select * from ta
select * from tb--函數
create function dbo.fn_m(@vchstring varchar(1000))
returns varchar(1000)
as
begin
declare @intstart int,@intlocation int
declare @s varchar(1000),@vchsubstring varchar(100)
select @intstart =1,@s=''
select @intlocation = charindex(',',@vchstring,@intstart)
while (@intlocation <>0 )
begin
select @vchsubstring=substring(@vchstring,@intstart,@intlocation-@intstart)
select @s=@s+cast([id] as varchar)+',' from ta where value=@vchsubstring
select @intstart = @intlocation +1
select @intlocation = charindex(',',@vchstring,@intstart)
end
return(@s)
end--刪除
drop table ta
drop table tb
drop function dbo.fn_mselect id, value=dbo.fn_m(value) from tb
--結果
id value
------------------
1 1,2,
2 2,3,
3 1,2,3,