declare @s varchar(1000) select @s=isnull(@s+',','')+ltrim(type)from (select distinct type from tb)t select @s
select distinct type from tb
--------------------------------- -- Author: htl258(Tony) -- Date : 2009-08-14 09:36:03 --------------------------------- --> 生成测试数据表:tbIf not object_id('[tb]') is null Drop table [tb] Go Create table [tb]([type] int) Insert tb Select 3 union all Select 4 union all Select 3 union all Select 1 union all Select 1 union all Select 1 union all Select 4 union all Select 2 Go --Select * from tb-->SQL查询如下: ;with t as ( select rn=row_number()over(order by getdate()),* from tb ),t1 as ( select type from t a where not exists( select 1 from t where type=a.type and rn<a.rn) ) select stuff((select ','+ltrim(type) from t1 for xml path('')),1,1,'') as type/* type ---------------------- 3,4,1,2(1 行受影响)*/
create table test(type int) insert into test select 3 union all select 4 union all select 3 union all select 1 union all select 1 union all select 4 union all select 2 goselect distinct(type) from testtype ----------- 1 2 3 4(所影响的行数为 4 行)
select top 1 stuff((select ','+ltrim([Type]) from aa group by [Type] for xml path('')),1,1,'') from aa
select top 1 stuff((select ','+ltrim([Type]) from 表 group by [Type] for xml path('')),1,1,'') from 表
DECLARE @VARCHAR VARCHAR(20) SELECT @VARCHAR=ISNULL(@VARCHAR+',','')+LTRIM(TYPE) FROM (SELECT DISTINCT * FROM TB)AS T SELECT @VARCHAR
select @s=isnull(@s+',','')+ltrim(type)from
(select distinct type from tb)t
select @s
---------------------------------
-- Author: htl258(Tony)
-- Date : 2009-08-14 09:36:03
---------------------------------
--> 生成测试数据表:tbIf not object_id('[tb]') is null
Drop table [tb]
Go
Create table [tb]([type] int)
Insert tb
Select 3 union all
Select 4 union all
Select 3 union all
Select 1 union all
Select 1 union all
Select 1 union all
Select 4 union all
Select 2
Go
--Select * from tb-->SQL查询如下:
;with t as
(
select rn=row_number()over(order by getdate()),*
from tb
),t1 as
(
select type
from t a
where not exists(
select 1
from t
where type=a.type
and rn<a.rn)
)
select stuff((select ','+ltrim(type) from t1 for xml path('')),1,1,'') as type/*
type
----------------------
3,4,1,2(1 行受影响)*/
create table test(type int)
insert into test
select 3 union all
select 4 union all
select 3 union all
select 1 union all
select 1 union all
select 4 union all
select 2 goselect distinct(type) from testtype
-----------
1
2
3
4(所影响的行数为 4 行)
select top 1 stuff((select ','+ltrim([Type]) from 表 group by [Type] for xml path('')),1,1,'') from 表
SELECT @VARCHAR=ISNULL(@VARCHAR+',','')+LTRIM(TYPE) FROM (SELECT DISTINCT * FROM TB)AS T
SELECT @VARCHAR