34,575
社区成员
发帖
与我相关
我的任务
分享
use Tempdb
go
--> --> 听雨停了-->生成测试数据
if not object_id(N'Tempdb..#tab') is null
drop table #tab
Go
Create table #tab([A] nvarchar(22),[B] nvarchar(22),[C] nvarchar(22),[D] nvarchar(31))
Insert #tab
select N'a1',N'b1',N'c1',N'd1/da' union all
select N'a2',N'b2',N'c2',N'd22/da/da22' union all
select N'a3',N'b3',N'c3',N'd3'
GO
;WITH cte AS (
SELECT a.A, a.B, a.C,b.col AS D,ROW_NUMBER() OVER(PARTITION BY a.A, a.B, a.C ORDER BY b.col) AS rn from #tab a
OUTER APPLY dbo.f_splitSTR(a.D,'/') b
)
SELECT CASE rn WHEN 1 THEN a ELSE NULL END AS a,
CASE rn WHEN 1 THEN b ELSE NULL END AS b,
CASE rn WHEN 1 THEN c ELSE NULL END AS c,
d
FROM cte
--创建字符串分割函数
CREATE FUNCTION [dbo].[f_splitSTR](
@s varchar(8000), --待分拆的字符串
@split varchar(10) --数据分隔符
)RETURNS @re TABLE(col varchar(100))
AS
BEGIN
DECLARE @splitlen int
SET @splitlen=LEN(@split+'a')-2
WHILE CHARINDEX(@split,@s)>0
BEGIN
INSERT @re VALUES(LEFT(@s,CHARINDEX(@split,@s)-1))
SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'')
END
INSERT @re VALUES(@s)
RETURN
END
GO
--测试数据
if not object_id(N'Tempdb..#tab') is null
drop table #tab
Go
Create table #tab([A] nvarchar(22),[B] nvarchar(22),[C] nvarchar(22),[D] nvarchar(31))
Insert #tab
select N'a1',N'b1',N'c1',N'd1/da' union all
select N'a2',N'b2',N'c2',N'd22/da/da22' union all
select N'a3',N'b3',N'c3',N'd3'
GO
--表连接函数得到结果
SELECT a.A, a.B, a.C,b.col AS D from #tab a
OUTER APPLY dbo.f_splitSTR(a.D,'/') b
--A B C D
------------------------ ---------------------- ---------------------- ----------------------------------------------------------------------------------------------------
--a1 b1 c1 d1
--a1 b1 c1 da
--a2 b2 c2 d22
--a2 b2 c2 da
--a2 b2 c2 da22
--a3 b3 c3 d3
use Tempdb
go
--> --> 中国风(Roy)生成測試數據
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([A] nvarchar(22),[B] nvarchar(22),[C] nvarchar(22),[D] nvarchar(31))
Insert #T
select N'a1',N'b1',N'c1',N'd1/da' union all
select N'a2',N'b2',N'c2',N'd22/da/da22' union all
select N'a3',N'b3',N'c3',N'd3'
GO
SELECT CASE WHEN b.number=1 THEN a.[A] END AS a ,CASE WHEN b.number=1 THEN a.[B] END AS [B],CASE WHEN b.number=1 THEN a.[C] END AS [C],
[D] = SUBSTRING(a.[D], b.number,
CHARINDEX('/', a.[D] + '/', b.number) - b.number)
FROM #T AS a
INNER JOIN master.dbo.spt_values AS b ON b.type = 'P'
AND CHARINDEX('/','/' + a.[D],b.number) = b.number
AND b.number > 0;
use Tempdb
go
--> --> 中国风(Roy)生成測試數據
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([A] nvarchar(22),[B] nvarchar(22),[C] nvarchar(22),[D] nvarchar(31))
Insert #T
select N'a1',N'b1',N'c1',N'd1/da' union all
select N'a2',N'b2',N'c2',N'd22/da/da22' union all
select N'a3',N'b3',N'c3',N'd3'
Go
Select CASE WHEN d.number=1 THEN a.a END AS a
,CASE WHEN d.number=1 THEN a.b END AS b
,CASE WHEN d.number=1 THEN a.c END AS c
,b.Col.query('r/c[position()=sql:column("d.number")]').value('.','varchar(50)') AS D
FROM #T AS a
CROSS APPLY(values(CONVERT(XML,'<r><c>'+REPLACE(a.[D],'/','</c><c>')+'</c></r>'))) AS b(Col)
CROSS APPLY ( SELECT c.number
FROM master.dbo.spt_values AS c
WHERE c.type = 'P'
AND c.number >= 1
AND b.Col.exist('r/c[position()=sql:column("c.number")]') = 1
) AS d(number)
/*
a b c D
a1 b1 c1 d1
NULL NULL NULL da
a2 b2 c2 d22
NULL NULL NULL da
NULL NULL NULL da22
a3 b3 c3 d3
*/