create proc up_delieve ( @str varchar(255) ) as set nocount on create table #tbl ( bz varchar(2) default('bz'), id int identity(1,1), value varchar(2) ) declare @a int=1 declare @new varchar(155) set @new=@str while @a<=len(@str) begin declare @s varchar(1) select @s=left(@new,1) select @new=right(@new,len(@new)-1) insert #tbl(value)values(@s) set @a=@a+1 end declare @sql varchar(max) set @sql='' select @sql=@sql+',[字段'+ltrim(id)+']=max(case when id='+ltrim(id) +' then value end)' from #tbl group by id exec('select bz'+@sql+' from #tbl group by bz') set nocount off goexec up_delieve '3214'/* bz 字段1 字段2 字段3 字段4 ---- ---- ---- ---- ---- bz 3 2 1 4 警告: 聚合或其他 SET 操作消除了 Null 值。*/这种字符串之间没任何间隔的 不好整
create proc up_delieve ( @str varchar(255) ) as set nocount on create table #tbl ( bz varchar(2) default('bz'), id int identity(1,1), value varchar(2) ) declare @a int=1 declare @new varchar(155) set @new=@str while @a<=len(@str) begin declare @s varchar(1) select @s=left(@new,1) select @new=right(@new,len(@new)-1) insert #tbl(value)values(@s) set @a=@a+1 end declare @sql varchar(max) set @sql='' select @sql=@sql+',[字段'+ltrim(id)+']=max(case when id='+ltrim(id) +' then value end)' from #tbl group by id exec('select bz'+@sql+' from #tbl group by bz') set nocount off goexec up_delieve '32145345432'/* bz 字段1 字段2 字段3 字段4 字段5 字段6 字段7 字段8 字段9 字段10 字段11 ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- bz 3 2 1 4 5 3 4 5 4 3 2 警告: 聚合或其他 SET 操作消除了 Null 值。*/
写个函数CREATE FUNCTION [dbo].[fn_SplitTest](@Input NVARCHAR(MAX)) RETURNS @TABLE TABLE([Id] INT IDENTITY(1,1), [Value] NVARCHAR(MAX)) AS BEGIN DECLARE @i INT =1 WHILE @i<=LEN(@Input) BEGIN INSERT INTO @TABLE([Value]) VALUES(SUBSTRING(@Input,@i,1)) SET @i=@i+1 END RETURN END ---测试SELECT * FROM [dbo].[fn_SplitTest]('143445956795')--结果 Id Value ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1 1 2 4 3 3 4 4 5 4 6 5 7 9 8 5 9 6 10 7 11 9 12 5(12 行受影响)
select SUBSTRING(@a,1,1),SUBSTRING(@a,2,1),SUBSTRING(@a,3,1),SUBSTRING(@a,4,1)
create proc up_delieve
(
@str varchar(255)
)
as
set nocount on
create table #tbl
(
bz varchar(2) default('bz'),
id int identity(1,1),
value varchar(2)
)
declare @a int=1
declare @new varchar(155)
set @new=@str
while @a<=len(@str)
begin
declare @s varchar(1)
select @s=left(@new,1)
select @new=right(@new,len(@new)-1)
insert #tbl(value)values(@s)
set @a=@a+1
end
declare @sql varchar(max)
set @sql=''
select
@sql=@sql+',[字段'+ltrim(id)+']=max(case when id='+ltrim(id)
+' then value end)'
from
#tbl
group by
id
exec('select bz'+@sql+' from #tbl group by bz')
set nocount off
goexec up_delieve '3214'/*
bz 字段1 字段2 字段3 字段4
---- ---- ---- ---- ----
bz 3 2 1 4
警告: 聚合或其他 SET 操作消除了 Null 值。*/这种字符串之间没任何间隔的 不好整
create proc up_delieve
(
@str varchar(255)
)
as
set nocount on
create table #tbl
(
bz varchar(2) default('bz'),
id int identity(1,1),
value varchar(2)
)
declare @a int=1
declare @new varchar(155)
set @new=@str
while @a<=len(@str)
begin
declare @s varchar(1)
select @s=left(@new,1)
select @new=right(@new,len(@new)-1)
insert #tbl(value)values(@s)
set @a=@a+1
end
declare @sql varchar(max)
set @sql=''
select
@sql=@sql+',[字段'+ltrim(id)+']=max(case when id='+ltrim(id)
+' then value end)'
from
#tbl
group by
id
exec('select bz'+@sql+' from #tbl group by bz')
set nocount off
goexec up_delieve '32145345432'/*
bz 字段1 字段2 字段3 字段4 字段5 字段6 字段7 字段8 字段9 字段10 字段11
---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
bz 3 2 1 4 5 3 4 5 4 3 2
警告: 聚合或其他 SET 操作消除了 Null 值。*/
RETURNS @TABLE TABLE([Id] INT IDENTITY(1,1),
[Value] NVARCHAR(MAX))
AS
BEGIN
DECLARE @i INT =1
WHILE @i<=LEN(@Input)
BEGIN
INSERT INTO @TABLE([Value])
VALUES(SUBSTRING(@Input,@i,1))
SET @i=@i+1
END
RETURN
END
---测试SELECT * FROM [dbo].[fn_SplitTest]('143445956795')--结果
Id Value
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 1
2 4
3 3
4 4
5 4
6 5
7 9
8 5
9 6
10 7
11 9
12 5(12 行受影响)