34,838
社区成员




if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[DATETIME] datetime,[CODE] varchar(1))
insert [tb]
select 1,'2012-7-7','A' union all
select 1,'2012-7-7','B' union all
select 1,'2012-7-17','C' union all
select 1,'2012-7-24','D' union all
select 2,'2012-7-11','A' union all
select 2,'2012-7-11','B' union all
select 3,'2012-7-16','A' union all
select 3,'2012-7-16','B' union all
select 3,'2012-7-17','C' union all
select 3,'2012-7-23','D'
go
declare @sql varchar(8000)
select @sql=isnull(@sql+',','')
+'max(case when code='''+code+''' then convert(varchar(10),datetime,120) end) as ['+code+']'
from
(select distinct code from tb) t
exec('select id,'+@sql+' from tb group by id')
/**
id A B C D
----------- ---------- ---------- ---------- ----------
1 2012-07-07 2012-07-07 2012-07-17 2012-07-24
2 2012-07-11 2012-07-11 NULL NULL
3 2012-07-16 2012-07-16 2012-07-17 2012-07-23
(3 行受影响)
**/
--1. 创建处理函数
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go
create function dbo.f_str(@id int)
returns varchar(8000)
as
begin
declare @r varchar(8000)
set @r = ''
select @r = @r + ',' + value from tb where id=@id
return stuff(@r, 1, 1, '')
end
go
-- 调用函数
SELECt id, value = dbo.f_str(id) FROM tb GROUP BY id
drop table tb
drop function dbo.f_str
/*
id value
----------- -----------
1 aa,bb
2 aaa,bbb,ccc
(所影响的行数为2 行)
*/
create table tb(ID int, DATETIME datetime ,code varchar(2))
insert into tb values('1','2012-7-7','A')
insert into tb values('1','2012-7-7','B')
insert into tb values('1','2012-7-17','C')
insert into tb values('1','2012-7-24','D' )
go
select ID, CODE,[DATETIME]=stuff((select ','+[DATETIME] from tb where ID=tb.ID for xml path('')), 1, 1, '')
from tb
group by ID
/*
ID A B C D
----------- --------------------
1 2012-7-7 2012-7-7 2012-7-17 2012-7-24