create table Test (name nvarchar(50),dt datetime,title nvarchar(50))
insert Test
select 'Bill','2010-9-13','go to hawill' union all
select 'Bill','2010-9-14','go to cc' union all
select 'Bill','2010-9-15','jump ' union all
select 'Bill','2010-9-16','捉迷藏' union all
select 'Bill','2010-9-17','过家家' union all
select 'jobs','2010-9-16','introduce apple' union all
select 'jobs','2010-9-17','go back china.' union all
select 'micc','2010-9-13','写真' union all
select 'micc','2010-9-14','写电影' union all
select 'micc','2010-9-15','参加派对' union all
select 'micc','2010-9-16','sing' union all
select 'micc','2010-9-17','joke' union all
select 'micc','2010-9-18','lauff'
delete Test
--
input :parameter
@dt
output
2010-9-13 2010-9-14 2010-9-15 2010-9-16 2010-9-17 2010-9-18 2010-9-19
Bill go to hawill go to cc jump 捉迷藏 过家家
jobs introduce apple go back china.
micc 写真 写电影 参加派对 sing joke lauff declare @dt datetime
set @dt=getdate()
declare @i int
set @i=datepart(Weekday, @dt)
if(@i=1)
set @i=8
set @i=@i-2set @dt=dateadd(d,-@i, @dt)
-- convert( varchar(20),@dt , 112)
declare @dt1 datetime
declare @sdt1 varchar(20)
declare @dt2 datetime
declare @sdt2 varchar(20)
declare @dt3 datetime
declare @sdt3 varchar(20)
declare @dt4 datetime
declare @sdt4 varchar(20)
declare @dt5 datetime
declare @sdt5 varchar(20)
declare @dt6 datetime
declare @sdt6 varchar(20)
declare @dt7 datetime
declare @sdt7 varchar(20)
set @dt1=dateadd(d,0,@dt);
set @dt2=dateadd(d,1,@dt);
set @dt3=dateadd(d,2,@dt);
set @dt4=dateadd(d,3,@dt);
set @dt5=dateadd(d,4,@dt);
set @dt6=dateadd(d,5,@dt);
set @dt7=dateadd(d,6,@dt);
set @sdt1=convert( varchar(20),@dt1 , 112)
set @sdt2=convert( varchar(20),@dt2 , 112)
set @sdt3=convert( varchar(20),@dt3 , 112)
set @sdt4=convert( varchar(20),@dt4 , 112)
set @sdt5=convert( varchar(20),@dt5 , 112)
set @sdt6=convert( varchar(20),@dt6 , 112)
set @sdt7=convert( varchar(20),@dt7 , 112)
select [name],
case when @sdt1=convert( varchar(20),dt , 112) then title else null end as 'Monday' ,
case when @sdt2=convert( varchar(20),dt , 112) then title else null end as 'Tuesday' ,
case when @sdt3=convert( varchar(20),dt , 112) then title else null end as 'Wednesday' ,
case when @sdt4=convert( varchar(20),dt , 112) then title else null end as 'Thursday' ,
case when @sdt5=convert( varchar(20),dt , 112) then title else null end as 'Friday' ,
case when @sdt6=convert( varchar(20),dt , 112) then title else null end as 'Saturday' ,
case when @sdt7=convert( varchar(20),dt , 112) then title else null end as 'Sunday'
from test
insert Test
select 'Bill','2010-9-13','go to hawill' union all
select 'Bill','2010-9-14','go to cc' union all
select 'Bill','2010-9-15','jump ' union all
select 'Bill','2010-9-16','捉迷藏' union all
select 'Bill','2010-9-17','过家家' union all
select 'jobs','2010-9-16','introduce apple' union all
select 'jobs','2010-9-17','go back china.' union all
select 'micc','2010-9-13','写真' union all
select 'micc','2010-9-14','写电影' union all
select 'micc','2010-9-15','参加派对' union all
select 'micc','2010-9-16','sing' union all
select 'micc','2010-9-17','joke' union all
select 'micc','2010-9-18','lauff'
delete Test
--
input :parameter
@dt
output
2010-9-13 2010-9-14 2010-9-15 2010-9-16 2010-9-17 2010-9-18 2010-9-19
Bill go to hawill go to cc jump 捉迷藏 过家家
jobs introduce apple go back china.
micc 写真 写电影 参加派对 sing joke lauff declare @dt datetime
set @dt=getdate()
declare @i int
set @i=datepart(Weekday, @dt)
if(@i=1)
set @i=8
set @i=@i-2set @dt=dateadd(d,-@i, @dt)
-- convert( varchar(20),@dt , 112)
declare @dt1 datetime
declare @sdt1 varchar(20)
declare @dt2 datetime
declare @sdt2 varchar(20)
declare @dt3 datetime
declare @sdt3 varchar(20)
declare @dt4 datetime
declare @sdt4 varchar(20)
declare @dt5 datetime
declare @sdt5 varchar(20)
declare @dt6 datetime
declare @sdt6 varchar(20)
declare @dt7 datetime
declare @sdt7 varchar(20)
set @dt1=dateadd(d,0,@dt);
set @dt2=dateadd(d,1,@dt);
set @dt3=dateadd(d,2,@dt);
set @dt4=dateadd(d,3,@dt);
set @dt5=dateadd(d,4,@dt);
set @dt6=dateadd(d,5,@dt);
set @dt7=dateadd(d,6,@dt);
set @sdt1=convert( varchar(20),@dt1 , 112)
set @sdt2=convert( varchar(20),@dt2 , 112)
set @sdt3=convert( varchar(20),@dt3 , 112)
set @sdt4=convert( varchar(20),@dt4 , 112)
set @sdt5=convert( varchar(20),@dt5 , 112)
set @sdt6=convert( varchar(20),@dt6 , 112)
set @sdt7=convert( varchar(20),@dt7 , 112)
select [name],
case when @sdt1=convert( varchar(20),dt , 112) then title else null end as 'Monday' ,
case when @sdt2=convert( varchar(20),dt , 112) then title else null end as 'Tuesday' ,
case when @sdt3=convert( varchar(20),dt , 112) then title else null end as 'Wednesday' ,
case when @sdt4=convert( varchar(20),dt , 112) then title else null end as 'Thursday' ,
case when @sdt5=convert( varchar(20),dt , 112) then title else null end as 'Friday' ,
case when @sdt6=convert( varchar(20),dt , 112) then title else null end as 'Saturday' ,
case when @sdt7=convert( varchar(20),dt , 112) then title else null end as 'Sunday'
from test
Bill NULL go to cc NULL NULL NULL NULL NULL
Bill NULL NULL jump NULL NULL NULL NULL
Bill NULL NULL NULL 捉迷藏 NULL NULL NULL
Bill NULL NULL NULL NULL 过家家 NULL NULL
jobs NULL NULL NULL introduce apple NULL NULL NULL
jobs NULL NULL NULL NULL go back china. NULL NULL
micc 写真 NULL NULL NULL NULL NULL NULL
micc NULL 写电影 NULL NULL NULL NULL NULL
micc NULL NULL 参加派对 NULL NULL NULL NULL
micc NULL NULL NULL sing NULL NULL NULL
micc NULL NULL NULL NULL joke NULL NULL
micc NULL NULL NULL NULL NULL lauff NULL
drop table Testcreate table Test (name nvarchar(50),dt datetime,title nvarchar(50))
insert Test
select 'Bill','2010-9-13','go to hawill' union all
select 'Bill','2010-9-14','go to cc' union all
select 'Bill','2010-9-15','jump ' union all
select 'Bill','2010-9-16','捉迷藏' union all
select 'Bill','2010-9-17','过家家' union all
select 'jobs','2010-9-16','introduce apple' union all
select 'jobs','2010-9-17','go back china.' union all
select 'micc','2010-9-13','写真' union all
select 'micc','2010-9-14','写电影' union all
select 'micc','2010-9-15','参加派对' union all
select 'micc','2010-9-16','sing' union all
select 'micc','2010-9-17','joke' union all
select 'micc','2010-9-18','lauff' set language us_english
select [name],max(case when datename(dw,dt)='Monday' then title else '' end) 'Monday',
max(case when datename(dw,dt)='Tuesday' then title else '' end) 'Tuesday',
max(case when datename(dw,dt)='Wednesday' then title else '' end) 'Wednesday',
max(case when datename(dw,dt)='Thursday' then title else '' end) 'Thursday',
max(case when datename(dw,dt)='Friday' then title else '' end) 'Friday',
max(case when datename(dw,dt)='Saturday' then title else '' end) 'Saturday',
max(case when datename(dw,dt)='Sunday' then title else '' end) 'Sunday'
from test
group by [name]
--结果
Bill go to hawill go to cc jump 捉迷藏 过家家
jobs introduce apple go back china.
micc 写真 写电影 参加派对 sing joke lauff
set language us_english
select [name],max(case when datename(dw,dt)='Monday' then title else '' end) 'Monday',
max(case when datename(dw,dt)='Tuesday' then title end) 'Tuesday',
max(case when datename(dw,dt)='Wednesday' then title end) 'Wednesday',
max(case when datename(dw,dt)='Thursday' then title end) 'Thursday',
max(case when datename(dw,dt)='Friday' then title end) 'Friday',
max(case when datename(dw,dt)='Saturday' then title end) 'Saturday',
max(case when datename(dw,dt)='Sunday' then title end) 'Sunday'
from test
group by [name]结果
Bill go to hawill go to cc jump 捉迷藏 过家家 NULL NULL
jobs NULL NULL introduce apple go back china. NULL NULL
micc 写真 写电影 参加派对 sing joke lauff NULL
insert #Test
select 'Bill','2010-9-13','go to hawill' union all
select 'Bill','2010-9-14','go to cc' union all
select 'Bill','2010-9-15','jump ' union all
select 'Bill','2010-9-16','捉迷藏' union all
select 'Bill','2010-9-17','过家家' union all
select 'jobs','2010-9-16','introduce apple' union all
select 'jobs','2010-9-17','go back china.' union all
select 'micc','2010-9-13','写真' union all
select 'micc','2010-9-14','写电影' union all
select 'micc','2010-9-15','参加派对' union all
select 'micc','2010-9-16','sing' union all
select 'micc','2010-9-17','joke' union all
select 'micc','2010-9-18','lauff'
set language englishselect * from (
select name,datename(dw,dt) as dayweek,title from #Test
) a
pivot(max(title) for dayweek in([Monday],[Tuesday],[Wednesday],[Thursday],[Friday],[Saturday],[Sunday])) as pvtset language 简体中文
Changed language setting to us_english.
name Monday Tuesday Wednesday Thursday Friday Saturday Sunday
-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
Bill go to hawill go to cc jump 捉迷藏 过家家 NULL NULL
jobs NULL NULL NULL introduce apple go back china. NULL NULL
micc 写真 写电影 参加派对 sing joke lauff NULL(3 行受影响)已将语言设置更改为 简体中文。
if object_id('Test') is not null
drop table testcreate table Test (name nvarchar(50),dt datetime,title nvarchar(50))
insert Test
select 'Bill','2010-9-13','go to hawill' union all
select 'Bill','2010-9-14','go to cc' union all
select 'Bill','2010-9-15','jump ' union all
select 'Bill','2010-9-16','捉迷藏' union all
select 'Bill','2010-9-17','过家家' union all
select 'jobs','2010-9-16','introduce apple' union all
select 'jobs','2010-9-17','go back china.' union all
select 'micc','2010-9-13','写真' union all
select 'micc','2010-9-14','写电影' union all
select 'micc','2010-9-15','参加派对' union all
select 'micc','2010-9-16','sing' union all
select 'micc','2010-9-17','joke' union all
select 'micc','2010-9-18','lauff'
SELECT [name],MAX(Monday) AS 'Monday'
,MAX(Tuesday)AS 'Tuesday'
,MAX(Wendesday) AS 'Wendesday'
,MAX(Thursday) AS 'Thursday'
,MAX(Friday)AS 'Friday'
,MAX(Saturday) AS 'Saturday'
,MAX(Sunday)AS 'Sunday'
FROM
(
SELECT [name],
CASE WHEN dt='2010-09-13 00:00:00.000' THEN title ELSE null END AS 'Monday',
CASE WHEN dt='2010-09-14 00:00:00.000' THEN title ELSE null END AS 'Tuesday',
CASE WHEN dt='2010-09-15 00:00:00.000' THEN title ELSE null END AS 'Wendesday',
CASE WHEN dt='2010-09-16 00:00:00.000' THEN title ELSE null END AS 'Thursday',
CASE WHEN dt='2010-09-17 00:00:00.000' THEN title ELSE null END AS 'Friday',
CASE WHEN dt='2010-09-18 00:00:00.000' THEN title ELSE null END AS 'Saturday',
CASE WHEN dt='2010-09-19 00:00:00.000' THEN title ELSE null END AS 'Sunday'
FROM Test
) AS temp
GROUP BY [name]