22,209
社区成员
发帖
与我相关
我的任务
分享
WITH a(A01,A02,A03) AS (
SELECT '100*200',7,N'202,301,204,205,308,401,505' UNION ALL
SELECT '200*123',3,N'203,206,307'
),
b AS (
SELECT
a.A01,
a.A02,
CAST(('<root><row>' + REPLACE(A03,',','</row><row>')+'</row></root>') AS XML) AS MyXML
FROM a
)
SELECT
b.A01,
c.A02,
c.A03
FROM b
OUTER APPLY(
SELECT
1 AS A02,
T.c.value('.','nvarchar(max)') AS A03
FROM b.MyXML.nodes('/root/row') T(c)
) c
create table tb
(
A01 varchar(50),
A02 int ,
A03 varchar(1000)
)
insert into tb values('100*200',7, '202,301,204,205,308,401,505'),
('200*123',3,'203,206,307')
go
select t.A01 'A01(规格)',
1 'A02(总数)',
SUBSTRING(t.A03, s.number ,CHARINDEX(',',t.A03+',',s.number)-s.number) as 'A03(房间号)'
from tb t,master..spt_values s
where s.number >=1
and s.type = 'P'
and SUBSTRING(','+t.A03,s.number,1) = ','
/*
A01(规格) A02(总数) A03(房间号)
100*200 1 202
100*200 1 301
100*200 1 204
100*200 1 205
100*200 1 308
100*200 1 401
100*200 1 505
200*123 1 203
200*123 1 206
200*123 1 307
*/
WITH
/* 测试数据
a(A01,A02,A03) AS (
SELECT '100*200',7,N'202,301,204,205,308,401,505' UNION ALL
SELECT '200*123',3,N'203,206,307'
), */
b AS (
SELECT a.A01,
a.A03,
n.i,
ROW_NUMBER() OVER(PARTITION BY A01 ORDER BY n.i) rn
FROM a
JOIN (SELECT number i
FROM master..spt_values
WHERE type='p'
AND number > 0
) n
ON SUBSTRING(a.A03,i,1) = N','
)
SELECT b2.A01,
1 A02,
CASE WHEN b1.i IS NULL THEN
LEFT(b2.A03, b2.i-1)
ELSE
SUBSTRING(b2.A03, b1.i+1, b2.i-b1.i-1)
END
FROM b b2
LEFT JOIN b b1
ON b2.A01 = B1.A01
AND b2.rn = b1.rn+1
A01 A02 A03
------- ----------- ---------------------------
100*200 1 202
100*200 1 301
100*200 1 204
100*200 1 205
100*200 1 308
100*200 1 401
200*123 1 203
200*123 1 206
create FUNCTION fn_split(@n NVARCHAR(max))
returns @t TABLE(n varchar(100))
AS
BEGIN
DECLARE @s nvarchar(MAX)
DECLARE @c nvarchar(100)
SET @s=@n+',';
WHILE LEN(@s)>0
BEGIN
SET @c=SUBSTRING(@s,0,CHARINDEX(',',@s));
SET @s=STUFF(@s,1,LEN(@c),'');
IF @s LIKE ',%'
SET @s=STUFF(@s,1,1,'');
INSERT INTO @t SELECT @c;
END
RETURN ;
END
go
DECLARE @t TABLE(id INT ,n NVARCHAR(max))
INSERT INTO @t SELECT 1,'1,2' UNION ALL SELECT 2,'1,2,3';
SELECT a.id,ap.* FROM @T a OUTER APPLY (SELECT * FROM dbo.fn_split(n)) ap
/*
id n
----------- ----------------------------------------------------------------------------------------------------
1 1
1 2
2 1
2 2
2 3
*/