请教行转列的sql语句
数据表：SGMSCB
字段：
SCB001(varchar) SCB002（varchar） SCB003（numeric）
张三 2007-08-05 10:57:04.933 1000
张三 2007-08-05 11:57:04.933 2000
李四 2007-08-06 08:57:04.933 4000
李四 2007-08-07 09:57:04.933 3000
...................
想得到的结果：
SCB001 2007-08-05 2007-08-06 2007-08-07 ....
张三 3000 0（or null） 0（or null）
李四 0（or null）4000 3000
[解决办法]
--如果SCB002是varchar
Declare @S Varchar(8000)
Select @S = ' Select SCB001 '
Select @S = @S + ', SUM(Case Left(SCB002, 10) When ' ' ' + SCB002 + ' ' ' Then SCB003 Else 0 End) As [ ' + SCB002 + '] '
From (Select Distinct Left(SCB002, 10) As SCB002 From SGMSCB) A Order By SCB002
Select @S = @S + ' From SGMSCB Group By SCB001 '
EXEC(@S)
[解决办法]
declare @sql varchar(8000)
set @sql = 'select SCB001 '
select @sql = @sql + ',sum(case substring(SCB002,1,10) as SCB002 when ' ' '+ substring(SCB002,1,10) + ' ' ' then SCB003j end) [ '+ substring(SCB002,1,10) + '] '
from (select distinct substring(SCB002,1,10) as SCB002 from SGMSCB) as a
select @sql = @sql+ ' from SGMSCB group by SCB001 '
exec(@sql)
