34,588
社区成员
发帖
与我相关
我的任务
分享
if object_id('[T]') is not null drop table [T]
go
create table [T]([col1] varchar(5),[col2] varchar(50),[col3] datetime,[col4] varchar(50))
insert [T]
select 'Silly','1,2465,345,4','2009-11-10','A1,BC,C,1D'
--------------开始查询--------------------------
Select ID=IDENTITY(INT,1,1),
a.Col1,COl2=substring(a.Col2,C.number,charindex(',',a.Col2+',',C.number)-C.number),
a.col3 INTO #TA
FROM T A
JOIN master..spt_values C ON C.type='p' and substring(','+a.COl2,C.number,1)=','
Select ID=IDENTITY(INT,1,1),
a.Col1,
a.col3,
col4=substring(a.Col4,B.number,charindex(',',a.Col4+',',B.number)-B.number) INTO #TB
from
T a join master..spt_values b
ON B.type='p' AND substring(','+a.COl4,b.number,1)=','
SELECT A.COL1,A.COL2,A.COL3,B.COL4 FROM #TA A,#TB B WHERE A.ID=B.ID
--DROP TABLE T,#TA,#TB
(所影响的行数为 1 行)
(所影响的行数为 4 行)
(所影响的行数为 4 行)
COL1 COL2 COL3 COL4
----- -------------------------------------------------- ------------------------------------------------------ --------------------------------------------------
Silly 1 2009-11-10 00:00:00.000 A1
Silly 2465 2009-11-10 00:00:00.000 BC
Silly 345 2009-11-10 00:00:00.000 C
Silly 4 2009-11-10 00:00:00.000 1D
(所影响的行数为 4 行)
if object_id('[T]') is not null drop table [T]
go
create table [T]([col1] varchar(5),[col2] varchar(50),[col3] datetime,[col4] varchar(50))
insert [T]
select 'Silly','1,2,3,4','2009-11-10','A,BC,C,D'
--------------开始查询--------------------------
Select ID=IDENTITY(INT,1,1),
a.Col1,COl2=substring(a.Col2,C.number,charindex(',',a.Col2+',',C.number)-C.number),
a.col3 INTO #TA
FROM T A
JOIN master..spt_values C ON C.type='p' and substring(','+a.COl2,C.number,1)=','
Select ID=IDENTITY(INT,1,1),
a.Col1,
a.col3,
col4=substring(a.Col4,B.number,charindex(',',a.Col4+',',B.number)-B.number) INTO #TB
from
T a join master..spt_values b
ON B.type='p' AND substring(','+a.COl4,b.number,1)=','
SELECT A.COL1,A.COL2,A.COL3,B.COL4 FROM #TA A,#TB B WHERE A.ID=B.ID
COL1 COL2 COL3 COL4
----- -------------------------------------------------- ------------------------------------------------------ --------------------------------------------------
Silly 1 2009-11-10 00:00:00.000 A
Silly 2 2009-11-10 00:00:00.000 BC
Silly 3 2009-11-10 00:00:00.000 C
Silly 4 2009-11-10 00:00:00.000 D
(所影响的行数为 4 行)
declare @tb table(A nvarchar(10),B nvarchar(10),C nvarchar(10),D nvarchar(10))if object_id('Tempdb..#Num') is not null
drop table #Num
go
select top 100 ID=Identity(int,1,1) into #Num from syscolumns a,syscolumns b
Select
a.Col1,COl2=substring(a.Col2,b.ID,charindex(',',a.Col2+',',b.ID)-b.ID)
from
Tab a,#Num b
where
charindex(',',','+a.Col2,b.ID)=b.ID --也可用 substring(','+a.COl2,b.ID,1)=','