sql server 2005中分隔列中数据
--建立辅助表SET NOCOUNT ON;
USE AdventureWorks;
GO
IF OBJECT_ID('dbo.Nums') IS NOT NULL
DROP TABLE dbo.Nums;
GO
CREATE TABLE dbo.Nums(n INT NOT NULL PRIMARY KEY);
DECLARE @max AS INT, @rc AS INT;
SET @max = 1000000;
SET @rc = 1;
INSERT INTO Nums VALUES(1);
WHILE @rc * 2 <= @max
BEGIN
INSERT INTO dbo.Nums SELECT n + @rc FROM dbo.Nums;
SET @rc = @rc * 2;
END
INSERT INTO dbo.Nums
SELECT n + @rc FROM dbo.Nums WHERE n + @rc <= @max;
--示例表
IF OBJECT_ID('dbo.Arrays') IS NOT NULL
DROP TABLE dbo.Arrays;
GO
CREATE TABLE dbo.Arrays
(
arrid VARCHAR(10) NOT NULL PRIMARY KEY,
array VARCHAR(8000) NOT NULL
)
INSERT INTO Arrays(arrid, array) VALUES('A', '20,22,25,25,14');
INSERT INTO Arrays(arrid, array) VALUES('B', '30,33,28');
INSERT INTO Arrays(arrid, array) VALUES('C', '12,10,8,12,12,13,12,14,10,9');
INSERT INTO Arrays(arrid, array) VALUES('D', '-4,-6,-4,-2');
--实施方法
SELECT arrid, substring(array,n,charindex(',',array+',',n)-n), n
FROM dbo.Arrays
JOIN dbo.Nums
ON n <= LEN(array)
AND SUBSTRING(','+array, n, 1) = ','
/**//*
select arrid,charindex(',',array+',',n)-n from dbo.Arrays JOIN dbo.Nums
ON n <= LEN(array)
AND SUBSTRING(','+array, n, 1) = ','
*/
/**//*
SELECT arrid, array, n
FROM dbo.Arrays
JOIN dbo.Nums
ON n <= LEN(array)
AND SUBSTRING(array, n, 1) = ','
*/
页:
[1]