关于Sql行转列有关问题

/*ID  Name     Number AddTime                 UserID7    时尚新闻    1    2010-02-01 00:00:00.000    1108    童装新闻    2    2010-02-01 00:00:00.000    1108    童装新闻    3    2010-02-02 00:00:00.000    1108    童装新闻    4    2010-02-03 00:00:00.000    1109    经销商动态    NULL    NULL    11010    面料市场    1    2010-02-01 00:00:00.000    11011    棉花市场    1    2010-02-01 00:00:00.000    11012    纱线行情    NULL    NULL    11013    羊毛市场    NULL    NULL    11014    市场透析    NULL    NULL    11015    缝纫设备    1    2010-02-01 00:00:00.000    11016    原料市场    NULL    NULL    11017    针织市场    NULL    NULL    11018    护照知识    NULL    NULL    11019    爱的教育    1    2010-02-01 00:00:00.000    11020    爱心大使    1    2010-02-01 00:00:00.000    11021    超级导购    NULL    NULL    11022    行业信息    1    2010-02-01 00:00:00.000    11023    外贸资讯    NULL    NULL    110...*/

UserID AddTime    时尚新闻 童装新闻 面料市场 棉花市场 ...110    2010-02-01 1        2        1        1110    2010-02-02 0        3        0        0110    2010-02-03 0        4        0        0111    2010-02-01 .......111    2010-02-02 .......

declare @sql varchar(500)set @sql = 'select AddTime'select @[email protected]+',max(case Name when '''+[Name]+''' then isnull(Number,0) end) ['+[Name]+']'from (select distinct Name from NewsView) as aset @sql = @sql +' from NewsView group by AddTime'exec(@sql)

declare @sql varchar(500)set @sql = 'select UserID , convert(varchar(10),AddTime,120) AddTime'select @[email protected]+',sum(case Name when '''+[Name]+''' then Number else 0 end) ['+[Name]+']'from (select distinct Name from NewsView) as aset @sql = @sql +' from NewsView group by UserID , convert(varchar(10),AddTime,120)'exec(@sql)
------解决方案--------------------
--动态行列转换
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 '张三','语文',78 union all
select '张三','数学',87 union all
select '张三','英语',82 union all
select '张三','物理',90 union all
select '李四','语文',65 union all
select '李四','数学',77 union all
select '李四','英语',65 union all
select '李四','物理',85  

--静态实现方法：
select student,
      MAX(case course when '语文' then score else 0 end) as 语文,
      MAX(case course when '数学' then score else 0 end) as 数学,
      MAX(case course when '英语' then score else 0 end) as 英语,
      MAX(case course when '物理' then score else 0 end) as 物理,
      SUM(score) as 总分,
      cast(AVG(score) as numeric(3,1)) as 平均分
from class group by student

--动态实现方法：
go
if OBJECT_ID('p_change')is not null
drop proc p_change
go
create proc p_change  
as
declare @name varchar(max)
set @name=''
select @[email protected]+','+course+'=max(case when course='+quotename(course,'''')
+' then score else 0 end)'
+CHAR(10)+CHAR(13)
from class group by course
print @name
select @name='select [email protected]+',sum(score) as 总分'+' from class group by student'
print @name
exec(@name)

exec p_change



------解决方案--------------------

SQL code
declare @sql varchar(8000)