一条SQL语句的问题。希望能帮帮我
我有一个表A 数据结构为
id name
1 1|2
2 3|4|5
我想要结果为
id name
1 1
1 2
2 3
2 4
2 5
怎么写？
[解决办法]
--drop table A
create table a(id int,name varchar(30))
insert into A
select 1, '1
[解决办法]
2' union all
select 2, '3
[解决办法]
4
[解决办法]
5'
select id,
--a.name,
SUBSTRING(A.name, number ,CHARINDEX('
[解决办法]
',a.name+'
[解决办法]
',number)-number) as name
from A ,master..spt_values s
where s.number >=1
and s.type = 'P'
and SUBSTRING('
[解决办法]
'+A.name,s.number,1) = '
[解决办法]
'
/*
idname
11
12
23
24
25
*/
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-11-13 10:30:18
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
--Dec 28 2012 20:23:12
--Copyright (c) Microsoft Corporation
--Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([id] int,[name] varchar(5))
insert [A]
select 1,'1
[解决办法]
2' union all
select 2,'3
[解决办法]
4
[解决办法]
5'
--------------开始查询--------------------------
select
id,
SUBSTRING(a.[name],number,CHARINDEX('
[解决办法]
',a.[name]+'
[解决办法]
',number)-number) as [name]
from
[A] a,master..spt_values
where
number >=1 and number<=len(a.[name])
and type='p'
and substring('
[解决办法]
'+a.[name],number,1)='
[解决办法]
'
----------------结果----------------------------
/*
id name
----------- -----
1 1
1 2
2 3
2 4
2 5
*/
create table t(id int,name varchar(30))
insert into t
select 1, '1
[解决办法]
2' union all
select 2, '3
[解决办法]
4
[解决办法]
5'
with tb as
(
select id,name=cast( SUBSTRING(name+'
[解决办法]
',1,charindex('
[解决办法]
',name+'
[解决办法]
')-1) as nvarchar(max))
,splitname=cast ( STUFF(name+'
[解决办法]
',1,charindex('
[解决办法]
',name+'
[解决办法]
'),'') as nvarchar(max))
from t
union all
select id,cast (SUBSTRING(splitname,1,charindex('
[解决办法]
',splitname)-1) as nvarchar(max))
,cast(STUFF(splitname,1,charindex('
------解决方案--------------------
',splitname),'') as nvarchar(max))
from tb
where charindex('
[解决办法]
',splitname)>0
)
select id,name
from tb
order by id,name
/*
idname
11
12
23
24
25
*/
