练习行列转换--sql server 2000

依据csdn高手写的自己练习一下方便以后查找

--Creator:Gongl
--Date:2009-1-8
--sql server 2000
--学习行转列，为了进一步了解动态sql拼接（单双三引号）
--几种类型
--Numeric(10,2) 指字段是数字型,长度为10 小数为两位
--varchar和nvarchar的区别
--1.从存储方式上，nvarchar是按字符存储的，而 varchar是按字节存储的；
--2.从存储量上考虑， varchar比较节省空间，因为存储大小为字节的实际长度，而 nvarchar是双字节存储；
--3.在使用上，如果存储内容都是英文字符而没有汉字等其他语言符号，建议使用varchar；含有汉字的使用nvarchar，因为nvarchar是使用Unicode编码，即统一的字符编码标准，会减少乱码的出现几率；

----行转列
--创建测试数据
if object_id('idl') is not null drop table idl
create table idl(name varchar(10),subject nvarchar(10),score numeric(4,1))
insert into idl
select 'anny','数学',95.5 union all
select 'anny','语文',90 union all
select 'anny','英语',99 union all
select 'anny','asp.net',100 union all
select 'anny','sqlserver',100 union all
select 'jenny','数学',94.5 union all
select 'jenny','语文',59.5 union all
select 'jenny','asp.net',100

 

--静态方法1
select [name],
 max(case subject when '数学' then score else 0 end) [数学],
 max(case subject when '语文' then score else 0 end) [语文],
 max(case subject when '英语' then score else 0 end) [英语],
 max(case subject when 'asp.net' then score else 0 end) [asp.net],
 max(case subject when 'sqlserver' then score else 0 end) [sqlserver]
from idl
group by [name]

--静态方法2
select [name],
 max(isnull((case subject when '数学' then score end),0)) [数学],
 max(isnull((case subject when '语文' then score end),0)) [语文],
 max(isnull((case subject when '英语' then score end),0)) [英语],
 isnull(max(case subject when 'asp.net' then score end),0) [asp.net],
 isnull(max(case subject when 'sqlserver' then score end),0) [sqlserver]
from idl
group by [name]

--动态方法
--三个单引号，其中有两个单引号是转义字符，两个单引号相当于一个单引号，还有一个单引号是连接字符串用的
declare  @sql varchar(4000)--定义变量
set @sql=''--给变量赋空值
select @sql='select [name]'--给变量赋值
select @sql=@sql+',max(isnull((case subject when '''+subject+''' then score end),0))['+subject+']'
from (select distinct subject from idl) a
select @sql=@sql+' from idl group by [name]'
print @sql
exec (@sql)

--列转行
--创建测试数据
if object_id('idl') is not null drop table idl
create table idl(name varchar(10),asp numeric(4,1),sqlserver numeric(4,1),数学 numeric(4,1),英语 numeric(4,1),语文 numeric(4,1))
insert into idl
select 'anny',100,95,99,98,56 union all
select 'jenny',91,94,88,87,59

--静态方法一
select *
from (select [name],subject='asp',score=[asp] from idl union all
 select [name],subject='sqlserver',score=[sqlserver] from idl union all
 select [name],subject='数学',score=[数学] from idl union all
 select [name],subject='英语',score=[英语] from idl union all
 select [name],subject='语文',score=[语文] from idl
)a
order by [name],[subject]

--动态方法一
declare @sql varchar(4000)
select @sql=isnull(@sql+' union all ','')+'select [name],[course]='+quotename(name,'''')--isnull(@s+' union all ','') 去掉字符串@s中第一个union all
+',[score]='+quotename(name)+' from idl'
from syscolumns where ID=object_id('idl') and name not in('name')--排除不转换的列
order by Colid
exec('select * from ('+@sql+')t order by [name],[course]')----增加一个排序