昨天刚帖过,创建一个拆分函数:
CREATE FUNCTION dbo.splitstring(@str varchar(4000),@c varchar(10))
RETURNS @t table(s varchar(100))
AS
BEGIN
DECLARE @s varchar(4000)
SET @s=@str
WHILE CHARINDEX(@c,@s)>0
BEGIN
INSERT INTO @t(s) VALUES(LEFT(@s,CHARINDEX(@c,@s)-1))
SET @s=RIGHT(@s,LEN(@s)-CHARINDEX(@c,@s))
END
INSERT INTO @t(s) VALUES(@s)
RETURN
END
GO
--调用:
select * from dbo.splitstring('23,abc,ed,ffyy',',')
结果集:
23
abc
ed
ffyy
CREATE FUNCTION dbo.splitstring(@str varchar(4000),@c varchar(10))
RETURNS @t table(s varchar(100))
AS
BEGIN
DECLARE @s varchar(4000)
SET @s=@str
WHILE CHARINDEX(@c,@s)>0
BEGIN
INSERT INTO @t(s) VALUES(LEFT(@s,CHARINDEX(@c,@s)-1))
SET @s=RIGHT(@s,LEN(@s)-CHARINDEX(@c,@s))
END
INSERT INTO @t(s) VALUES(@s)
RETURN
END
GO
--调用:
select * from dbo.splitstring('23,abc,ed,ffyy',',')
结果集:
23
abc
ed
ffyy
go
insert test values('张三','语文',80)
insert test values('张三','数学',86)
insert test values('张三','英语',75)
insert test values('李四','语文',78)
insert test values('李四','数学',85)
insert test values('李四','英语',78)declare @sql varchar(8000)
set @sql = 'select name'
select @sql = @sql + ',sum(case km when '''+km+''' then cj end) ['+km+']'
from (select distinct km from test) as a
select @sql = @sql+' from test group by name'
exec(@sql)drop table test
@delimiter varchar(10))----分隔符
RETURNS @report TABLE (a varchar(20) ,b int identity(1,1))
AS
BEGIN
declare @str1 varchar(20) ,@len int
set @str=replace(@str,' ','')---去掉所有空格
set @delimiter=replace(@delimiter,' ','')
set @str=@str+','
while CHARINDEX(@delimiter+@delimiter,@str)>0 set @str=replace(@str,@delimiter+@delimiter,@delimiter)
while not( CHARINDEX(@delimiter,@str) =0 or @str=@delimiter)
begin
select @len=CHARINDEX(@delimiter,@str)
set @str1=left(@str,@len-1)
insert @report SELECT @str1
select @str=right(@str,len(@str)-1-len(@str1))
end
RETURN
END
GO
--调用:
select * into # from dbo.f_split_str_tab('23,abc,ed,ffyy',',')
declare @ varchar(8000)
set @=''
select @=@+',['+a+']=max(case a when '''+a+''' then a end) '
from #
select @='['+right(@,len(@)-1)
exec('select '+@+' from # ')drop table #/*
23 abc ed ffyy
-------------------- -------------------- -------------------- --------------------
23 abc ed ffyy
*/