declare @t table(a varchar(10)) insert into @t select 'a' insert into @t select 'b' insert into @t select 'c' insert into @t select 'd'declare @a varchar(1000) set @a='' select @a=@a+','+a from @t print stuff(@a,1,1,'')
declare @str varchar(8000) set @str='' select @str=@str+fieldname from tablename print @str
Create Table A(ID Int,Name Varchar(10)) Insert A Select 1,'A' Union All Select 2,'C' Union All Select 3,'D' GO Declare @S Varchar(1000) Select @S='' Select @S=@S+','+Name From A Select @S=Stuff(@S,1,1,'') Select @S GO Drop Table A --Reslut /* A,C,D */
上面的稍做改動即可啊。 Create Table A(ID Int,Name Varchar(10)) Insert A Select 1,'AA' Union All Select 2,'BB' Union All Select 3,'CC' GO Declare @S Varchar(1000) Select @S='' Select @S=@S+Name From A Select @S GO Drop Table A --Reslut /* AABBCC */
标准的1句, 2000不行2005没有任何问题:-- 示例数据 DECLARE @t TABLE(id int, value varchar(10)) INSERT @t SELECT 1, 'aa' UNION ALL SELECT 1, 'bb' UNION ALL SELECT 2, 'aaa' UNION ALL SELECT 2, 'bbb' UNION ALL SELECT 2, 'ccc'-- 查询处理 SELECT * FROM( SELECT DISTINCT id FROM @t )A OUTER APPLY( SELECT [values]= STUFF(REPLACE(REPLACE( ( SELECT value FROM @t N WHERE id = A.id FOR XML AUTO ), '<N value="', ','), '"/>', ''), 1, 1, '') )N/*--结果 id values ----------- ---------------- 1 aa,bb 2 aaa,bbb,ccc(2 行受影响) --*/
select @a=''
select @a=@+列 from 表
--print @a
select @a
insert into @t select 'a'
insert into @t select 'b'
insert into @t select 'c'
insert into @t select 'd'declare @a varchar(1000)
set @a=''
select @a=@a+','+a from @t
print stuff(@a,1,1,'')
set @str=''
select @str=@str+fieldname from tablename
print @str
Insert A Select 1,'A'
Union All Select 2,'C'
Union All Select 3,'D'
GO
Declare @S Varchar(1000)
Select @S=''
Select @S=@S+','+Name From A
Select @S=Stuff(@S,1,1,'')
Select @S
GO
Drop Table A
--Reslut
/*
A,C,D
*/
我在这里补充一下,
比如有人一个表只有一列(A列)
我不想通过游标,就把A列的数据集连起来
1行 ‘AA’
2行 'BB'
3行 ‘CC’
不能过游标 就能联成 AABBCC
谢了
Create Table A(ID Int,Name Varchar(10))
Insert A Select 1,'AA'
Union All Select 2,'BB'
Union All Select 3,'CC'
GO
Declare @S Varchar(1000)
Select @S=''
Select @S=@S+Name From A
Select @S
GO
Drop Table A
--Reslut
/*
AABBCC
*/
DECLARE @t TABLE(id int, value varchar(10))
INSERT @t SELECT 1, 'aa'
UNION ALL SELECT 1, 'bb'
UNION ALL SELECT 2, 'aaa'
UNION ALL SELECT 2, 'bbb'
UNION ALL SELECT 2, 'ccc'-- 查询处理
SELECT *
FROM(
SELECT DISTINCT
id
FROM @t
)A
OUTER APPLY(
SELECT [values]= STUFF(REPLACE(REPLACE(
(
SELECT value FROM @t N
WHERE id = A.id
FOR XML AUTO
), '<N value="', ','), '"/>', ''), 1, 1, '')
)N/*--结果
id values
----------- ----------------
1 aa,bb
2 aaa,bbb,ccc(2 行受影响)
--*/
http://community.csdn.net/Expert/topic/4800/4800752.xml?temp=.9975702
【交流】SQL 2005溢用之:合并列值
http://community.csdn.net/Expert/topic/4800/4800963.xml?temp=4.940432E-02
【交流】SQL 2005溢用之:分拆列值
Select @S=@S+','+Name From A
中的Name From A
是根据什么来写的.什么意思来着