有表tb, 如下:
id value
----------- -----------
1 aa,bb
2 aaa,bbb,ccc
欲按id,分拆value列, 分拆后结果如下:
id value
----------- --------
1 aa
1 bb
2 aaa
2 bbb
2 ccc
创建表和测试数据
create
table
tb(id
int
,value
varchar
(30))
insert
into
tb
values
(1,
'aa,bb'
)
insert
into
tb
values
(2,
'aaa,bbb,ccc'
)
go
|
1. 旧的解决方法(sql server 2000)
SELECT
TOP
8000 id = IDENTITY(
int
, 1, 1)
INTO
#
FROM
syscolumns a, syscolumns b
SELECT
A.id,
SUBSTRING
(A.[
values
], B.id, CHARINDEX(
','
, A.[
values
] +
','
, B.id) - B.id)
FROM
tb A, # B
WHERE
SUBSTRING
(
','
+ A.[
values
], B.id, 1) =
','
DROP
TABLE
#
|
2. 新的解决方法(sql server 2005)
SELECT
A.id, B.value
FROM
(
SELECT
id, [value] =
CONVERT
(xml,
'<root><v>'
+
REPLACE
([value],
','
,
'</v><v>'
) +
'</v></root>'
)
FROM
tb
)A
OUTER
APPLY(
SELECT
value = N.v.value(
'.'
,
'varchar(100)'
)
FROM
A.[value].nodes(
'/root/v'
) N(v)
)B
DROP
TABLE
tb
|
/*
id value
----------- ------------------------------
1 aa
1 bb
2 aaa
2 bbb
2 ccc