csdn混的,不应该不 会
-
- /******************************************************************************************************************************************************
- 以学生成绩为例子,比较形象易懂
-
- 整理人:中国风(Roy)
-
- 日期:2008.06.06
- ******************************************************************************************************************************************************/
-
-
-
-
- if not object_id('Class') is 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
-
- 动态:
-
- 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+' 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]
- 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
-
-
-
- 动态:
-
- 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]')
-
- 生成动态:
-
- 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),
- [总成绩]=sum([Score])
- from
- Class
- group by [Student]
-
- go
-
-
-
- 动态:
-
- declare @s nvarchar(4000)
- Select @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course]
- 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
- pivot
- (max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b
-
- 生成格式:
-
- /*
- Student 数学 物理 英语 语文 总成绩
-
- 李四 77 85 65 65 292
- 张三 87 90 82 78 337
-
- (2 行受影响)
- */
-
- go
-
-
-
-
- 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
-
-
-
- 动态:
-
- declare @s nvarchar(4000)
- select @s=isnull(@s+' union all ','')+'select [Student],[Course]='+quotename(Name,'''')
- +',[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
-
-
- 动态:
-
- 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
-
- (8 行受影响)
- */
posted @
2008-10-27 09:59
roboth
阅读(
233)
评论()
编辑
收藏
举报