CREATE TABLE tb(col1 varchar(10),col2 int) INSERT tb SELECT 'a',1 UNION ALL SELECT 'a',2 UNION ALL SELECT 'b',1 UNION ALL SELECT 'b',2 UNION ALL SELECT 'b',3 GO--合并处理函数 CREATE FUNCTION dbo.f_str(@col1 varchar(10)) RETURNS varchar(100) AS BEGIN DECLARE @re varchar(100) SET @re='' SELECT @re=@re+','+CAST(col2 as varchar) FROM tb WHERE col1=@col1 RETURN(STUFF(@re,1,1,'')) END GO--调用函数 SELECT col1,col2=dbo.f_str(col1) FROM tb GROUP BY col1
是只有1个字符串?? 1,2,3,4,5.还是有一个表? Tid ---- 1,2,3.。 3,2 4,5,3,2,1,6,7
还是说清楚吧, 我现在有个表1里字段id,该字段可以存多个id,它们都是以逗号隔开。还有一个表2里也有id字段,该字段只能存一个id现在要找出表2里的id都在表1里存在的记录。比如说: 表1 id 1,2,3 6 11,23 表2 id 1 2 5 8 23 我最后得到的结果就是 id 1 2 23
暈早說 select id from t2 where exists(select 1 from t1 where charindex(','+rtrim(t2.id)+',' , ','+rtrim(t1.id)+',')>0)
你早說6樓的需求,答案不就出來了么还拆什么字段。select id from t2 where exists(select 1 from t1 where charindex(','+rtrim(t2.id)+',' , ','+rtrim(t1.id)+',')>0)
楼主要的是一个split函数吧 create function splitstr(@source varchar(8000),@ varchar(4)) returns @temp table(F1 varchar(2000)) as begin declare @i int set @source=rtrim(ltrim(@source)) set @i=charindex(@,@source) while @i>1 begin insert @temp values(left(@source,@i)) set @source=subtring(@source,@i+1,len(@source)-@i) set @i=charindex(@,@source) end if @source<>'' insert @temp values(@source) returnend
不知道这样是否可以解决楼主的问题!
拆分表:--> --> (Roy)生成測試數據
if not object_id('Tab') is null drop table Tab Go Create table Tab([Col1] int,[COl2] nvarchar(5)) Insert Tab select 1,N'a,b,c' union all select 2,N'd,e' union all select 3,N'f' GoSQL2000用辅助表: 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)=',' SQL2005用Xml:select a.COl1,b.Col2 from (select Col1,COl2=convert(xml,'<root><v>'+replace(COl2,',','</v><v>')+'</v></root>') from Tab)a outer apply (select Col2=C.v.value('.','nvarchar(100)') from a.COl2.nodes('/root/v')C(v))b SQL05用CTE:;with roy as (select Col1,COl2=cast(left(Col2,charindex(',',Col2+',')-1) as nvarchar(100)),Split=cast(stuff(COl2+',',1,charindex(',',Col2+','),'') as nvarchar(100)) from Tab union all select Col1,COl2=cast(left(Split,charindex(',',Split)-1) as nvarchar(100)),Split= cast(stuff(Split,1,charindex(',',Split),'') as nvarchar(100)) from Roy where split>'' ) select COl1,COl2 from roy order by COl1 option (MAXRECURSION 0)生成结果: /* Col1 COl2 ----------- ----- 1 a 1 b 1 c 2 d 2 e 3 f */
declare @str varchar(1000) set @str = '43,53,1234,532,24,35' set @str = 'select '''+replace(@str,',',''',''')+'''' print @str exec (@str)
INSERT tb SELECT 'a',1
UNION ALL SELECT 'a',2
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',2
UNION ALL SELECT 'b',3
GO--合并处理函数
CREATE FUNCTION dbo.f_str(@col1 varchar(10))
RETURNS varchar(100)
AS
BEGIN
DECLARE @re varchar(100)
SET @re=''
SELECT @re=@re+','+CAST(col2 as varchar)
FROM tb
WHERE col1=@col1
RETURN(STUFF(@re,1,1,''))
END
GO--调用函数
SELECT col1,col2=dbo.f_str(col1) FROM tb GROUP BY col1
Tid
----
1,2,3.。
3,2
4,5,3,2,1,6,7
我现在有个表1里字段id,该字段可以存多个id,它们都是以逗号隔开。还有一个表2里也有id字段,该字段只能存一个id现在要找出表2里的id都在表1里存在的记录。比如说:
表1 id
1,2,3
6
11,23 表2
id
1
2
5
8
23 我最后得到的结果就是
id
1
2
23
select id from t2
where exists(select 1 from t1 where charindex(','+rtrim(t2.id)+',' , ','+rtrim(t1.id)+',')>0)
where exists(select 1 from t1 where charindex(','+rtrim(t2.id)+',' , ','+rtrim(t1.id)+',')>0)
create function splitstr(@source varchar(8000),@ varchar(4))
returns @temp table(F1 varchar(2000))
as
begin
declare @i int
set @source=rtrim(ltrim(@source))
set @i=charindex(@,@source)
while @i>1
begin
insert @temp values(left(@source,@i))
set @source=subtring(@source,@i+1,len(@source)-@i)
set @i=charindex(@,@source)
end
if @source<>'' insert @temp values(@source)
returnend
不知道这样是否可以解决楼主的问题!
if not object_id('Tab') is null
drop table Tab
Go
Create table Tab([Col1] int,[COl2] nvarchar(5))
Insert Tab
select 1,N'a,b,c' union all
select 2,N'd,e' union all
select 3,N'f'
GoSQL2000用辅助表:
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)=','
SQL2005用Xml:select
a.COl1,b.Col2
from
(select Col1,COl2=convert(xml,'<root><v>'+replace(COl2,',','</v><v>')+'</v></root>') from Tab)a
outer apply
(select Col2=C.v.value('.','nvarchar(100)') from a.COl2.nodes('/root/v')C(v))b
SQL05用CTE:;with roy as
(select Col1,COl2=cast(left(Col2,charindex(',',Col2+',')-1) as nvarchar(100)),Split=cast(stuff(COl2+',',1,charindex(',',Col2+','),'') as nvarchar(100)) from Tab
union all
select Col1,COl2=cast(left(Split,charindex(',',Split)-1) as nvarchar(100)),Split= cast(stuff(Split,1,charindex(',',Split),'') as nvarchar(100)) from Roy where split>''
)
select COl1,COl2 from roy order by COl1 option (MAXRECURSION 0)生成结果:
/*
Col1 COl2
----------- -----
1 a
1 b
1 c
2 d
2 e
3 f
*/
declare @str varchar(1000)
set @str = '43,53,1234,532,24,35'
set @str = 'select '''+replace(@str,',',''',''')+''''
print @str
exec (@str)
declare @str varchar(500)
select @str = '43,53,1234,532,24,52'
select @str = 'select name='''+replace(@str,',',''''+' union all select ''')+''''
exec (@str)/*结果
43
53
1234
532
24
52
*/
--***************行转列
declare @str varchar(500)
select @str = '43,53,1234,532,24,52'
select @str = 'select '''+replace(@str,',',''''+' as name, ''')+''''
exec (@str)/* 结果
43 53 1234 532 24 52
*/