CREATE TABLE [dbo].[ERPWorkRiZhi](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ContentStr] [nvarchar](1000) COLLATE Chinese_PRC_CI_AS NULL,
[Expect] [nvarchar](1000) COLLATE Chinese_PRC_CI_AS NULL,
[ExaminePeople] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[FactCompete] [nvarchar](500) COLLATE Chinese_PRC_CI_AS NULL,
[Windage] [nvarchar](500) COLLATE Chinese_PRC_CI_AS NULL,
[Reason] [nvarchar](500) COLLATE Chinese_PRC_CI_AS NULL,
[ameliorate] [nvarchar](500) COLLATE Chinese_PRC_CI_AS NULL,
[Re] [nvarchar](200) COLLATE Chinese_PRC_CI_AS NULL,
[UserName] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[TimeStr] [datetime] NULL,
[Isdel] [int] NULL)
declare @username nvarchar(20),@starttime datetime,@endtime datetime
set @username='用户1';set @starttime='2012/3/4 00:00:00'; set @endtime ='2012/3/10 23:59:00';
select case when datepart(dw,timestr)=1 then contentstr end as sunday ,
case when datepart(dw,timestr)=2 then contentstr end as monday ,
case when datepart(dw,timestr)=3 then contentstr end as tuesday ,
case when datepart(dw,timestr)=4 then contentstr end as Wednesday ,
case when datepart(dw,timestr)=5 then contentstr end as Thursday,
case when datepart(dw,timestr)=6 then contentstr end as Friday,
case when datepart(dw,timestr)=7 then contentstr end as Saturday
from (select contentstr,timestr from ERPWorkRiZhi where username=@username and timestr BETWEEN @starttime and @endtime) as a;这样转是转好了, 但是有个问题选出来的数据是这样的
sunday monday tuesday Wednesday Thursday Friday Saturday
内容1 NULL NULL NULL NULL NULL NULL
NULL 内容2 NULL NULL NULL NULL NULL
NULL 内容3 NULL NULL NULL NULL NULL
NULL NULL 内容4 NULL NULL NULL NULL能不能把NULL的去掉呢,内容向上提,请教如何操作?谢谢指导~
--行列互转
--1、行换列
if object_id('Class') is not null
drop table Class
Go
Create table Class(
[Student] nvarchar(2),
[Course] nvarchar(2),
[Score] int)
Insert Class
select N'张三',N'语文',78 union all
select N'张三',N'数学',87 union all
select N'张三',N'英语',82 union all
select N'张三',N'物理',90 union all
select N'李四',N'语文',65 union all
select N'李四',N'数学',77 union all
select N'李四',N'英语',65 union all
select N'李四',N'物理',85
Go--2000方法:
--动态:
declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename([Course])+'=max(case when [Course]='
+quotename([Course],'''')+' then [Score] else 0 end)'
from
Class group by[Course]
--select @s
exec('select [Student]'+@s+' from Class group by [Student]')
--生成静态:
select
[Student],
[数学]=max(case when [Course]='数学' then [Score] else 0 end),
[物理]=max(case when [Course]='物理' then [Score] else 0 end),
[英语]=max(case when [Course]='英语' then [Score] else 0 end),
[语文]=max(case when [Course]='语文' then [Score] else 0 end)
from
Class
group by [Student]
GO
--动态:declare @s nvarchar(4000)
Select @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course]
select @s
exec('select * from Class pivot (max([Score]) for [Course] in('+@s+'))b')--生成静态:
select *
from
Class
pivot
(max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b--生成格式:
/*
Student 数学 物理 英语 语文
------- ----------- ----------- ----------- -----------
李四 77 85 65 65
张三 87 90 82 78(2 行受影响)
*/go
--加上总成绩(学科平均分)--2000方法:
--动态:declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+'then [Score] else 0 end)'
from Class group by[Course]
exec('select [Student]'+@s+',[总成绩]=sum([Score]) from Class group by [Student]')--加多一列(学科平均分用avg([Score]))生成动态:select
[Student],
[数学]=max(case when [Course]='数学' then [Score] else 0 end),
[物理]=max(case when [Course]='物理' then [Score] else 0 end),
[英语]=max(case when [Course]='英语' then [Score] else 0 end),
[语文]=max(case when [Course]='语文' then [Score] else 0 end),
[总成绩]=([Score]) --加多一列(学科平均分用avg([Score]))
from
Class
group by [Student]go--2005方法:动态:declare @s nvarchar(4000)
Select @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course]
--isnull(@s+',','') 去掉字符串@s中第一个逗号exec('select [Student],'+@s+',[总成绩] from (select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a
pivot (max([Score]) for [Course] in('+@s+'))b ')--生成静态:select
[Student],[数学],[物理],[英语],[语文],[总成绩]
from
(select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a --平均分时用avg([Score])
pivot
(max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b 生成格式:/*
Student 数学 物理 英语 语文 总成绩
------- ----------- ----------- ----------- ----------- -----------
李四 77 85 65 65 292
张三 87 90 82 78 337(2 行受影响)
*/go--2、列转行
if not object_id('Class') is null
drop table Class
Go
Create table Class([Student] nvarchar(2),[数学] int,[物理] int,[英语] int,[语文] int)
Insert Class
select N'李四',77,85,65,65 union all
select N'张三',87,90,82,78
Go--2000:动态:declare @s nvarchar(4000)
select @s=isnull(@s+' union all ','')+'select [Student],[Course]='+quotename(Name,'''')--isnull(@s+' union all ','') 去掉字符串@s中第一个union all
+',[Score]='+quotename(Name)+' from Class'
from syscolumns where ID=object_id('Class') and Name not in('Student')--排除不转换的列
order by Colid
exec('select * from ('+@s+')t order by [Student],[Course]')--增加一个排序生成静态:
select *
from (select [Student],[Course]='数学',[Score]=[数学] from Class union all
select [Student],[Course]='物理',[Score]=[物理] from Class union all
select [Student],[Course]='英语',[Score]=[英语] from Class union all
select [Student],[Course]='语文',[Score]=[语文] from Class)t
order by [Student],[Course]go
--2005:动态:declare @s nvarchar(4000)
select @s=isnull(@s+',','')+quotename(Name)
from syscolumns where ID=object_id('Class') and Name not in('Student')
order by Colid
exec('select Student,[Course],[Score] from Class unpivot ([Score] for [Course] in('+@s+'))b')go
select
Student,[Course],[Score]
from
Class
unpivot
([Score] for [Course] in([数学],[物理],[英语],[语文]))b
生成格式:
/*
Student Course Score
------- ------- -----------
李四 数学 77
李四 物理 85
李四 英语 65
李四 语文 65
张三 数学 87
张三 物理 90
张三 英语 82
张三 语文 78
*/
不晓得你要什么样的结果,也没测试数据
给点参考资料吧
内容 内容 内容 NULL 内容 NULL 内容
NULL 内容 NULL NULL 内容 NULL 内容
NULL 内容 NULL NULL 内容 NULL 内容
就是按填的时间转化成周显示,同时内容都提到最顶部(NULL的不要)显示,
按我的想法应该是select (select contentstr from 表 where 日期=本周日) as 周日,(select contentstr from 表 where 日期=本周一) as 周一 from 表
但这样写的结果是 返回子查询不止一个,应该怎么改啊
declare @username nvarchar(20),@starttime datetime,@endtime datetime
set @username='用户1';
set @starttime='2012/3/4 00:00:00';
set @endtime ='2012/3/10 23:59:00';
select
isnull(case when datepart(dw,timestr)=1 then contentstr end ,'')as sunday ,
isnull(case when datepart(dw,timestr)=2 then contentstr end ,''as monday ,
isnull(case when datepart(dw,timestr)=3 then contentstr end ,''as tuesday ,
isnull(case when datepart(dw,timestr)=4 then contentstr end ,''as Wednesday ,
isnull(case when datepart(dw,timestr)=5 then contentstr end ,''as Thursday,
isnull(case when datepart(dw,timestr)=6 then contentstr end ,''as Friday,
isnull(case when datepart(dw,timestr)=7 then contentstr end ,''as Saturday from
(select contentstr,timestr from ERPWorkRiZhi
where username=@username and timestr
BETWEEN @starttime and @endtime) as a;
和你举的例子差不多,我把要数据分组(变成列) 按 周日-周六显示,比如周日有3条数据,周一有2条数据,其它为空,它出来的结果是这样的
sunday monday tuesday Wednesday Thursday Friday Saturday
内容 NULL NULL NULL NULL NULL NULL
内容 NULL NULL NULL NULL NULL NULL
内容 NULL NULL NULL NULL NULL NUL
NULL 内容 NULL NULL NULL NULL NULL
NULL 内容 NULL NULL NULL NULL NULL我希望的结果是
sunday monday tuesday Wednesday Thursday Friday Saturday
内容 内容 NULL NULL NULL NULL NULL
内容 内容 NULL NULL NULL NULL NULL
内容 NULL NULL NULL NULL NULL NUL
、
set @username='用户1';set @starttime='2012/3/4 00:00:00'; set @endtime ='2012/3/10 23:59:00';
select
max(case when datepart(dw,timestr)=1 then contentstr end) as sunday ,
max(case when datepart(dw,timestr)=2 then contentstr end) as monday ,
max(case when datepart(dw,timestr)=3 then contentstr end) as tuesday ,
max(case when datepart(dw,timestr)=4 then contentstr end) as Wednesday ,
max(case when datepart(dw,timestr)=5 then contentstr end) as Thursday,
max(case when datepart(dw,timestr)=6 then contentstr end) as Friday,
max(case when datepart(dw,timestr)=7 then contentstr end) as Saturday
from (select contentstr,timestr from ERPWorkRiZhi where username=@username and timestr BETWEEN @starttime and @endtime) as a;