在线等!MSSQL怎样将将某一列中的数据拆成一行一行的

chyde 2015-08-05 03:40:44
如题:
有一个表A,说明一下:A02字段里的数量,A03的明细里也会有相应个数用逗号分割开的房间号
A01(规格) A02(总数) A03(房间号)
----------------------------------------------------------------------------------------------------------------------------
100*200 7 202,301,204,205,308,401,505
200*123 3 203,206,307

....

现在想得到如下效果,全部拆分成数量为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 207
...全文
96 点赞 收藏 7
写回复
7 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
chyde 2015-08-05
引用 6 楼 airsoft 的回复:
另外一种方法,可以参考一下:

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

感谢!!
回复
Haytor 2015-08-05
另外一种方法,可以参考一下:

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

回复
chyde 2015-08-05
感谢大神!!
回复
试试这个:
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
*/
回复
Tiger_Zhao 2015-08-05
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
回复
Cherise_huang 2015-08-05
可以用动态行转列啊
回复
gw6328 2015-08-05


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
*/




回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2015-08-05 03:40
社区公告
暂无公告