declare @a varchar(8000) select @a=isnull(@a+',','')+b from [table] order by a select @a
DECLARE @STR VARCHAR(8000)SELECT @STR=ISNULL(@STR+',','')+b FROM (SELECT DISTINCT b FROM 表)AS TSELECT @STR
if not object_id('tb') is null drop table tb Go Create table tb([A] int,[B] nvarchar(1)) Insert tb select 1,N'a' union all select 2,N'b' union all select 3,N'c' Go Select distinct stuff((select ','+[B] from tb for xml path('')),1,1,'') from tb t /* a,b,c*/
能不用变量吗?数据很多,可能会超过varchar8000
declare @a varchar(8000) set @s='' select @a=@a+isnull(b,'')+',' from [table] order by a PRINT @a
超过8000的话 改成VARCHAR(MAX)
IF object_id('tempdb..#','U') IS NOT NULL DROP TABLE #; GOCREATE TABLE #( A int ,B varchar(30) ); goINSERT INTO # SELECT 1,'a' UNION ALL SELECT 2,'b' UNION ALL SELECT 3,'c' goDECLARE @Str varchar(8000)SELECT @Str = isnull(@Str + ',','') + B FROM #;SELECT @Str;--结果 /* (3 行受影响) a,b,c */
select distinct b from (select stuff((select ','+B from table for xml path('')),1,1,'') as b from table ) a
IF OBJECT_ID('dbo.tb') IS NOT NULL DROP TABLE dbo.tb; CREATE TABLE tb ( A INT IDENTITY, B VARCHAR(10) ); INSERT INTO tb SELECT 'a' UNION ALL SELECT 'b' UNION ALL SELECT 'c'DECLARE @s VARCHAR(50) SET @s = '' SELECT @s = @s + ',' + B FROM tb SELECT STUFF(@s,1,1,'') ------------- a,b,c(1 row(s) affected)
select @a=isnull(@a+',','')+b from [table] order by a
select @a
drop table tb
Go
Create table tb([A] int,[B] nvarchar(1))
Insert tb
select 1,N'a' union all
select 2,N'b' union all
select 3,N'c'
Go
Select
distinct
stuff((select ','+[B] from tb for xml path('')),1,1,'')
from tb t
/*
a,b,c*/
set @s=''
select @a=@a+isnull(b,'')+',' from [table] order by a
PRINT @a
IF object_id('tempdb..#','U') IS NOT NULL
DROP TABLE #;
GOCREATE TABLE #(
A int
,B varchar(30)
);
goINSERT INTO #
SELECT 1,'a' UNION ALL
SELECT 2,'b' UNION ALL
SELECT 3,'c'
goDECLARE @Str varchar(8000)SELECT @Str = isnull(@Str + ',','') + B
FROM #;SELECT @Str;--结果
/*
(3 行受影响)
a,b,c
*/
(select stuff((select ','+B from table for xml path('')),1,1,'') as b
from table ) a
IF OBJECT_ID('dbo.tb') IS NOT NULL
DROP TABLE dbo.tb;
CREATE TABLE tb
(
A INT IDENTITY,
B VARCHAR(10)
);
INSERT INTO tb SELECT 'a'
UNION ALL SELECT 'b'
UNION ALL SELECT 'c'DECLARE @s VARCHAR(50)
SET @s = ''
SELECT @s = @s + ',' + B FROM tb
SELECT STUFF(@s,1,1,'') -------------
a,b,c(1 row(s) affected)