if
object_id
(
'
tb
'
)
is
not
null
drop
table
tb
--
-->建表
create
table
tb(
[
name
]
varchar
(
4
),
[
value
]
varchar
(
9
))
insert
tb
select
'
张三
'
,
'
1,2,3,4
'
union
all
select
'
李四
'
,
'
a,s,d,f,g
'
--
1.2000/2005通用方法
SELECT
a.
[
name
]
,
[
value
]
=
SUBSTRING
(a.
[
value
]
,
number
,
CHARINDEX
(
'
,
'
,a.
[
value
]
+
'
,
'
,
number
)
-
b.
number
)
FROM
tb a
JOIN
master..spt_values b
ON
b.type
=
'
P
'
--
AND SUBSTRING(','+a.col,b.number,1)=',' --用此条件或下面的条件均可
AND
CHARINDEX
(
'
,
'
,
'
,
'
+
a.
[
value
]
,
number
)
=
number
--
2.2005以上新方法:
SELECT
a.
[
name
]
,b.
[
value
]
FROM
(
SELECT
[
name
]
,
[
value
]
=
CAST
(
'
<v>
'
+
REPLACE
(
[
value
]
,
'
,
'
,
'
</v><v>
'
)
+
'
</v>
'
AS
xml)
FROM
tb) a
OUTER
APPLY (
SELECT
[
value
]
=
T.C.value(
'
.
'
,
'
varchar(50)
'
)
FROM
a.
[
value
]
.nodes(
'
/v
'
)
AS
T(C)) b
--
> 查询结果
SELECT
*
FROM
tb
--
> 删除表格
DROP
TABLE
tb