34,838
社区成员




CREATE FUNCTION [dbo].[SplitStr]
(
@string nvarchar(max),
@symbol nvarchar(10)
)
RETURNS @table TABLE(id int identity,value nvarchar(max))
AS
begin
DECLARE @splitlen int
SET @splitlen=LEN(@symbol)-1
WHILE CHARINDEX(@symbol,@string)>0
BEGIN
INSERT @table(value) VALUES(LEFT(@string,CHARINDEX(@symbol,@string)-1))
SET @string=STUFF(@string,1,CHARINDEX(@symbol,@string)+@splitlen,'')
END
INSERT @table(value) VALUES(@string)
return
end
go
declare @tb table(id int identity,val nvarchar(max))
insert into @tb(val) values('"姓名":赵钱,"性别":男,"年龄":11'),('"姓名":王二,"性别":男,"年龄":31,"学历":大专'),('"姓名":张三,"性别":男,"年龄":21,"身高":170')
select * from @tb a
cross apply (
select pvt.[1] as fld,pvt.[2] as v from dbo.splitStr(val,',') s1
cross apply (
select id,replace(value,'"','') as v from dbo.splitStr(value,':')
) app
pivot (max(app.v) for app.id in ([1],[2])) pvt
) app
pivot (max(v) for fld in (姓名,性别,年龄,身高,学历)) pvt -- 字段列需要手动修改,这个不知道怎么自动变成字段名
declare @s nvarchar(max)='"姓名":赵钱,"性别":男,"年龄":11'
select @s=N'select '+ replace(replace(@s,':','='''),',',''',')+''''
exec (@s)
/*
姓名 性别 年龄
赵钱 男 11
*/