关于Sql行转列有关问题

关于Sql行转列问题
在一个试图中有如下数据

SQL code

/*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...*/

想让它变成

SQL code

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 .......

根据UserID以及AddTime分组

该怎么写SQL?
我这样写一直有问题

SQL code

declare @sql varchar(500)set @sql = 'select AddTime'select @sql=@sql+',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)

[解决办法]

SQL code

declare @sql varchar(500)set @sql = 'select UserID , convert(varchar(10),AddTime,120) AddTime'select @sql=@sql+',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 @name=@name+','+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 student'+@name+',sum(score) as 总分'+' from class group by student'
print @name
exec(@name)

exec p_change



[解决办法]

SQL codedeclare @sql varchar(8000)

关于Sql行转列有关问题

热点推荐