SQL Server 选取数据表的行作为列显示出来

--------------统计一列的某些行进行统计并将统计值作为列显示-----------------

--创建表test
create table test
(str_date int,
 str_num int,
 str_name varchar(10)
)
--插入数据
insert into test
select 1,34,'A' union all
select 1,21,'B' union all
select 2,23,'D' union all
select 2,32,'C' union all
select 3,56,'D' union all
select 3,19,'A' union all
select 4,26,'B' union all
select 4,38,'A'

--进行查询
declare @sqlstr varchar(8000)
select  @sqlstr = ''
select  @sqlstr = @sqlstr +',sum(case str_name when '''+str_name+''' then str_num else 0 end) as '+ str_name
from test
group by str_name
exec('select str_date'+@sqlstr+' from test group by str_date')

--结果显示
     strdate    A       B       C       D
          1        68      21     0       0
          2        0        0        32    23
          3        19      0        0      56
          4        38      26      0      0

--删除表test

drop table test

Help by : rivery

------------------------------将多列转变为多行-------------------------------------

--创建表
create table Info(ID int,sname varchar(20),Email varchar(100))
create table CP(ID int,course varchar(20),point int)
--插入数据
insert Info select 1,'Mike','123@163.com'
union all select 2,'Tom','456@sina.com'
union all select 3,'Bill','bill@yahoo.com.cn'
union all select 4,'Jerry','maybe123@hotmail.com'

insert CP select 1,'高数',72
union all select 1,'邓小平理论',88
union all select 1,'大学英语',69
union all select 1,'毛泽东思想概论',86
union all select 1,'C语言',89
union all select 1,'微观经济学',70

union all select 2,'宏观经济学',69
union all select 2,'大学英语',46
union all select 2,'Java技术',78
union all select 2,'数据结构',90

union all select 3,'高数',61
union all select 3,'大学英语',56

--进行查询
declare @s varchar(8000)
set @s=''
select @s=@s+',max(case aa when '''+rtrim(aa)+'''  then course else null end) as course'+rtrim(aa)+'
,max(case aa when '''+rtrim(aa)+'''  then point else null end) as point'
from (select *,aa=(select count(1) from cp where id = p.id and course <=p.course) from CP p )t
group by aa

select @s= ' select i.id,sname,Email,'+stuff(@s,1,1,'') +' from Info i inner join
(select *,aa=(select count(1) from CP where id = p.id and course <= p.course) from CP p)t
 on t.id = i.id group by i.id,i.sname,i.Email'
print stuff(@s,1,1,'')
exec (@s)

--结果显示
1 Mike 123@163.com          C语言     89  大学英语  69 邓小平理论 88 高数      72 毛泽东思想概论 86 微观经济学 70
2 Tom 456@sina.com        Java技术 78  大学英语 46  宏观经济学 69 数据结构 90 NULL NULL NULL NULL
3 Bill    bill@yahoo.com.cn 大学英语 56  高数          61  NULL NULL NULL NULL NULL NULL NULL NULL

--删除表
drop table Info
drop table CP