2008-05-02 07:46
2008-05-02 12:08
2008-05-02 13:53
2008-05-02 18:09
达到这种效果放到两个字段中去
日期 刷卡记录
2008-05-02 07:46 12:08 13:5318:09
2008-05-02 12:08
2008-05-02 13:53
2008-05-02 18:09
达到这种效果放到两个字段中去
日期 刷卡记录
2008-05-02 07:46 12:08 13:5318:09
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (dt datetime)
insert into #T
select '2008-05-02 07:46' union all
select '2008-05-02 12:08' union all
select '2008-05-02 13:53' union all
select '2008-05-02 18:09'select
日期=convert(char(10),dt,112),
刷卡记录=(stuff((select ' '+convert(varchar(5),dt,114) from #T where convert(char(10),dt,112)=convert(char(10),a.dt,112) for xml path('')),1,1,''))
from #T a group by convert(char(10),dt,112)/*
日期 刷卡记录
---------- -----------------------
20080502 07:46 12:08 13:53 18:09
*/
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (dt datetime)
insert into #T
select '2008-05-02 07:46' union all
select '2008-05-02 12:08' union all
select '2008-05-02 13:53' union all
select '2008-05-02 18:09'select
日期=convert(char(10),dt,120),
刷卡记录=(stuff((select ' '+convert(varchar(5),dt,114) from #T where convert(char(10),dt,120)=convert(char(10),a.dt,120) for xml path('')),1,1,''))
from #T a group by convert(char(10),dt,120)/*
日期 刷卡记录
---------- -----------------------
2008-05-02 07:46 12:08 13:53 18:09
*/
/******************************************************************************/
/*回复:20080530001总:00059 */
/*主题:行转列 */
/*作者:二等草 */
/******************************************************************************/set nocount on--数据--------------------------------------------------------------------------
create table [tb] ([d] datetime)
insert into [tb] select '2008-05-02 07:46'
insert into [tb] select '2008-05-02 12:08'
insert into [tb] select '2008-05-02 13:53'
insert into [tb] select '2008-05-02 18:09'
go--代码--------------------------------------------------------------------------
create function gets(@d char(10))
returns varchar(30)
as
begin
declare @s varchar(30)
select @s = isnull(@s+' ','')+convert(char(5),d,108) from tb where convert(char(10),d,120)=@d
return @s
end
go
select d=convert(char(10),d,120),t=dbo.gets(convert(char(10),d,120)) from tbgo/*结果--------------------------------------------------------------------------
d t
---------- ------------------------------
2008-05-02 07:46 12:08 13:53 18:09
2008-05-02 07:46 12:08 13:53 18:09
2008-05-02 07:46 12:08 13:53 18:09
2008-05-02 07:46 12:08 13:53 18:09
--清除------------------------------------------------------------------------*/
drop function gets
go
drop table tb
/******************************************************************************/
/*回复:20080530001总:00059 */
/*主题:行转列 */
/*作者:二等草 */
/******************************************************************************/set nocount on--数据--------------------------------------------------------------------------
create table [tb] ([d] datetime)
insert into [tb] select '2008-05-02 07:46'
insert into [tb] select '2008-05-02 12:08'
insert into [tb] select '2008-05-02 13:53'
insert into [tb] select '2008-05-02 18:09'
insert into [tb] select '2008-05-03 10:09'
insert into [tb] select '2008-05-03 18:09'
go--代码--------------------------------------------------------------------------
create function gets(@d char(10))
returns varchar(30)
as
begin
declare @s varchar(30)
select @s = isnull(@s+' ','')+convert(char(5),d,108) from tb where convert(char(10),d,120)=@d
return @s
end
go
select d,t=dbo.gets(d) from (select distinct d=convert(char(10),d,120) from tb) ago/*结果--------------------------------------------------------------------------
d t
---------- ------------------------------
2008-05-02 07:46 12:08 13:53 18:09
2008-05-03 10:09 18:09
--清除------------------------------------------------------------------------*/
drop function gets
go
drop table tb
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (dt datetime)
insert into #T
select '2008-05-02 07:46' union all
select '2008-05-02 12:08' union all
select '2008-05-02 13:53' union all
select '2008-05-02 18:09'select
日期=convert(char(10),dt,120),
刷卡记录=(stuff((select ' '+convert(varchar(5),dt,114) from #T where convert(char(10),dt,120)=convert(char(10),a.dt,120) for xml path('')),1,1,''))
from #T a group by convert(char(10),dt,120)/*
日期 刷卡记录
---------- -----------------------
2008-05-02 07:46 12:08 13:53 18:09
*/