请教行转列的sql语句

[firestoneman]

数据表：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

[xmlquit]

declare @sql varchar(8000)
set @sql = 'select SCB001'
select @sql = @sql + ',sum(case substring(SCB002,1,10) when '''+ substring(SCB002,1,10) +''' then cj end) ['+ substring(SCB002,1,10) +']'
from (select distinct substring(SCB002,1,10) from SGMSCB) as a
select @sql = @sql+' from SGMSCB group by SCB001'
exec(@sql)

[paoluo]

--如果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)

[xmlquit]

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)

[paoluo]

--創建測試環境
Create Table SGMSCB
(SCB001 varchar(20),
SCB002 varchar(30),
SCB003 numeric(10, 0))
--插入數據
Insert SGMSCB Select N'张三', '2007-08-05 10:57:04.933', 1000
Union All Select N'张三', '2007-08-05 11:57:04.933', 2000
Union All Select N'李四', '2007-08-06 08:57:04.933', 4000
Union All Select N'李四', '2007-08-07 09:57:04.933', 3000
GO
--測試
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)
GO
--刪除測試環境
Drop Table SGMSCB
--結果
/*
SCB001 2007-08-05 2007-08-06 2007-08-07
张三 3000 0 0
李四 0 4000 3000
*/

[firestoneman]

谢谢各位，先给分，再研究一下。

[lang8134]

create table # (
SCB001 varchar(10),
SCB002 varchar(50),
SCB003 numeric(10,2))

insert #
select '张三', '2007-08-05 10:57:04.933', 1000
union all
select '张三', '2007-08-05 11:57:04.933', 2000
union all
select '李四', '2007-08-06 08:57:04.933', 4000
union all
select '李四', '2007-08-07 09:57:04.933', 3000



declare @sql varchar(2000)

select @sql = 'select #.SCB001, '

select @sql = @sql + 'sum(case convert(varchar(10),#.SCB002) when '''+ t_rq.rq + ''' then SCB003 else 0 end ) as ''' + t_rq.rq +''','


from (select rq = convert(varchar(10),SCB002) from # group by convert(varchar(10),SCB002)) t_rq

select @sql = left(@sql,len(@sql) - 1) + ' from # group by #.SCB001'
print @sql
exec (@sql)

drop table #
---------------------------------------------------------
结果
SCB001 2007-08-05 2007-08-06 2007-08-07
-----------------------------------------------
李四 .00 4000.00 3000.00
张三 3000.00 .00 .00