---------------------------------------------------------------- -- Author :DBA_HuangZJ(发粪涂墙) -- Date :2014-05-08 11:50:53 -- Version: -- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) -- Apr 2 2010 15:48:46 -- Copyright (c) Microsoft Corporation -- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor) -- ---------------------------------------------------------------- --> 测试数据[huang] if object_id('[huang]') is not null drop table [huang] go create table [huang]([a] nvarchar(4),[b] nvarchar(10)) insert [huang] select 'X1','1,4,8' union all select 'X2','2' union all select 'X3','3,6' union all select 'X4','7' union all select 'X5','5' --------------生成数据--------------------------select
a.[a], SUBSTRING([b],number,CHARINDEX(',',[b]+',',number)-number) as [b] from [huang] a,master..spt_values where number >=1 and number<=len([b]) and type='p' and substring(','+[b],number,1)=',' ----------------结果---------------------------- /* a b ---- ---------- X1 1 X1 4 X1 8 X2 2 X3 3 X3 6 X4 7 X5 5 */
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-05-08 11:50:53
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
-- Apr 2 2010 15:48:46
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([a] nvarchar(4),[b] nvarchar(10))
insert [huang]
select 'X1','1,4,8' union all
select 'X2','2' union all
select 'X3','3,6' union all
select 'X4','7' union all
select 'X5','5'
--------------生成数据--------------------------select
a.[a],
SUBSTRING([b],number,CHARINDEX(',',[b]+',',number)-number) as [b]
from
[huang] a,master..spt_values
where
number >=1 and number<=len([b])
and type='p'
and substring(','+[b],number,1)=','
----------------结果----------------------------
/*
a b
---- ----------
X1 1
X1 4
X1 8
X2 2
X3 3
X3 6
X4 7
X5 5
*/