例如:Table(Id,date)
记录:
Id date
AA 2011-07-19 08:10:10
AA 2011-07-19 12:10:10
AA 2011-07-19 17:10:10
BB 2011-07-19 08:15:10
BB 2011-07-19 11:50:10现在想把记录显示如下,该怎么写? 谢谢!2011-07-19 AA 08:10:10 12:10:10 17:10:10
2011-07-19 BB 08:15:10 11:50:10 在线等待........
记录:
Id date
AA 2011-07-19 08:10:10
AA 2011-07-19 12:10:10
AA 2011-07-19 17:10:10
BB 2011-07-19 08:15:10
BB 2011-07-19 11:50:10现在想把记录显示如下,该怎么写? 谢谢!2011-07-19 AA 08:10:10 12:10:10 17:10:10
2011-07-19 BB 08:15:10 11:50:10 在线等待........
id, [date]=stuff((select ' '+[date] from tb t where id=t.id for xml path('')), 1, 1, '')
from
tb t
group by
id
--五列declare @sql varchar(8000)select *,rid=row_number() over (partition by id,convert(varchar(10),date,120) order by date)
into #tb
from [table]set @sql = 'select convert(varchar(10),date,120) date,id'
select @sql = @sql + ',max(case rid when '+ltrim(rid)+' then substring(convert(varchar(19),date,120),12,8) else '''' end) [col_'+ltrim(rid)+']'
from(select number as rid from master..spt_values
where type = 'p' and number between 1 and (select max(rid) from #tb)) t
select @sql = @sql + ' from #tb group by convert(varchar(10),date,120) date,id'
exec(@sql)
select
convert(varchar(10),date,120) as [date],id,
max(case id0 when 1 then convert(varchar(10),date,108) else '' end) as col1,
max(case id0 when 2 then convert(varchar(10),date,108) else '' end) as col2,
max(case id0 when 3 then convert(varchar(10),date,108) else '' end) as col3
from
(select id0=row_number()over(partition by id order by getdate()),* from tb)
t
group by
convert(varchar(10),date,120),id
select
convert(varchar(10),date,120) as [date],id, [date]=stuff((select ' '+convert(varchar(10),date,108) from tb t where id=t.id for xml path('')), 1, 1, '')
from
tb t
group by
id,convert(varchar(10),date,120) as [date],
insert a
select 'AA','2011-07-19 08:10:10' union all
select 'AA','2011-07-19 12:10:10' union all
select 'AA','2011-07-19 17:10:10' union all
select 'BB','2011-07-19 08:15:10' union all
select 'BB','2011-07-19 11:50:10'
declare @sql varchar(8000)
set @sql = 'select convert(varchar(10),date,23),Id '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' from tb group by Id,convert(varchar(10),date,23)'
exec(@sql)
select Id,convert(varchar(10),date,23),
fgid=stuff((select ','+convert(varchar(108),date,8) from a ta
where Id=a.Id and convert(varchar(10),date,23)=convert(varchar(10),a.date,23) for xml path('')),1,1,'')
from a
group by convert(varchar(10),date,23),Id/*
Id fgid
---- ---------- -----------------------------
AA 2011-07-19 08:10:10,12:10:10,17:10:10
BB 2011-07-19 08:15:10,11:50:10(2 行受影响)
*/)
insert a
select 'AA','2011-07-19 08:10:10' union all
select 'AA','2011-07-19 12:10:10' union all
select 'AA','2011-07-19 17:10:10' union all
select 'BB','2011-07-19 08:15:10' union all
select 'BB','2011-07-19 11:50:10'
declare @sql varchar(8000)
set @sql = 'select convert(varchar(10),date,23),Id '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' from tb group by Id,convert(varchar(10),date,23)'
exec(@sql)
select Id,convert(varchar(10),date,23),
fgid=stuff((select ','+convert(varchar(108),date,8) from a ta
where Id=a.Id and convert(varchar(10),date,23)=convert(varchar(10),a.date,23) for xml path('')),1,1,'')
from a
group by convert(varchar(10),date,23),Id/*
Id fgid
---- ---------- ----------------------
AA 2011-07-19 08:10:10,12:10:10,17:10:10
BB 2011-07-19 08:15:10,11:50:10(2 行受影响)
*/)
create Table a(Id varchar(2),date datetime)
insert a
select 'AA','2011-07-19 08:10:10' union all
select 'AA','2011-07-19 12:10:10' union all
select 'AA','2011-07-19 17:10:10' union all
select 'BB','2011-07-19 08:15:10' union all
select 'BB','2011-07-19 11:50:10'
godeclare @sql varchar(8000)select *,rid=row_number() over (partition by id,convert(varchar(10),date,120) order by date)
into #tb
from aset @sql = 'select convert(varchar(10),date,120) date,id'
select @sql = @sql + ',max(case rid when '+ltrim(rid)+' then substring(convert(varchar(19),date,120),12,8) else '''' end) [col_'+ltrim(rid)+']'
from(select number as rid from master..spt_values
where type = 'p' and number between 1 and (select max(rid) from #tb)) t
select @sql = @sql + ' from #tb group by convert(varchar(10),date,120),id'
exec(@sql)drop table a,#tb/***********date id col_1 col_2 col_3
---------- ---- ---------------- ---------------- ----------------
2011-07-19 AA 08:10:10 12:10:10 17:10:10
2011-07-19 BB 08:15:10 11:50:10 (2 行受影响)
2011-07-19 BB 08:15:10 11:50:10 意思是想一天一个id 一条记录,可能不同id 对应记录不同,以一天最多记录数的作为最大列...
insert a
select 'AA','2011-07-19 08:10:10' union all
select 'AA','2011-07-19 12:10:10' union all
select 'AA','2011-07-19 17:10:10' union all
select 'BB','2011-07-19 08:15:10' union all
select 'BB','2011-07-19 11:50:10'select Id,convert(varchar(10),date,23) as 日期,
所有列=stuff((select ','+convert(varchar(108),date,8) from a ta
where Id=a.Id and convert(varchar(10),date,23)=convert(varchar(10),a.date,23) for xml path('')),1,1,'')
from a
group by convert(varchar(10),date,23),Id/*
Id 日期 所有列
---- ---------- -----------------------------
AA 2011-07-19 08:10:10,12:10:10,17:10:10
BB 2011-07-19 08:15:10,11:50:10(2 行受影响)*/)
insert a
select 'AA','2011-07-19 08:10:10' union all
select 'AA','2011-07-19 12:10:10' union all
select 'AA','2011-07-19 17:10:10' union all
select 'BB','2011-07-19 08:15:10' union all
select 'BB','2011-07-19 11:50:10'
-- 原来的逗号改为空格了
select Id,convert(varchar(10),date,23) as 日期,
所有列=stuff((select ' '+convert(varchar(108),date,8) from a ta
where Id=a.Id and convert(varchar(10),date,23)=convert(varchar(10),a.date,23) for xml path('')),1,1,'')
from a
group by convert(varchar(10),date,23),Id/*
Id 日期 所有列
---- ---------- --------------------------
AA 2011-07-19 08:10:10 12:10:10 17:10:10
BB 2011-07-19 08:15:10 11:50:10(2 行受影响)*/)