日期:2014-05-17 浏览次数:20526 次
-----建表数据 CREATE TABLE [dbo].[stockinfo]( [stockcode] [int] NULL, [yybid] [int] NULL, [yybname] [nvarchar](50) NULL ) ON [PRIMARY] insert into [master].[dbo].[stockinfo] values(1,1234,'hello') insert into [master].[dbo].[stockinfo] values(1,1235,'world') insert into [master].[dbo].[stockinfo] values(2,10000,'good') insert into [master].[dbo].[stockinfo] values(2,10001,'better') insert into [master].[dbo].[stockinfo] values(2,10002,'best') insert into [master].[dbo].[stockinfo] values(3,9527,'ios') insert into [master].[dbo].[stockinfo] values(3,9528,'android') insert into [master].[dbo].[stockinfo] values(3,9529,'firefoxos') insert into [master].[dbo].[stockinfo] values(3,9530,'meego') insert into [master].[dbo].[stockinfo] values(3,9531,'BlackBerry') insert into [master].[dbo].[stockinfo] values(4,3389,'port')
--行列互转 --摘自中国风博客,引用请标明内容来源 --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], [数学]