求一个复杂的sql语句
表如下
id     title     value       date
-------------------------
1         aaa         10         2007-1-2
1         ccc         2           2007-3-4
2         ccc         4           2007-2-2
1         ddd         3           2007-1-1
2         ddd         3           2007-2-3
2         aaa         11         2007-4-2


我想生成如下的结果   把title做为表头，后面是value数值，最后计算id重复的总值，结果如下
id     aaa   ccc   ddd   sum
-----------------------
1       10     2       3         15
2       11     4       3         18


因为比较复杂。。高价求救


------解决方案--------------------
行转列，照抄一下给你~~~

Create Table A
(
[date] Varchar(10),
[quarter] Varchar(10),
[value] Numeric(10,1)
)
GO

Insert A Select '2006-7-29 ', 'Q1 ',0.2
Union All Select '2006-7-29 ', 'Q2 ',0.5
Union All Select '2006-7-29 ', 'Q3 ',0.3
Union All Select '2006-7-30 ', 'Q1 ',0.1
Union All Select '2006-7-30 ', 'Q2 ',0.2
Union All Select '2006-7-30 ', 'Q3 ',0.7
select * from a
GO

Declare @S Varchar(8000)
Set @S= ' '
Select @S=@S + ',SUM(Case [quarter] When ' ' ' + [quarter] + ' ' ' Then value Else 0 End) As ' + [quarter]
From A Group By [quarter] Order By [quarter]
Select @S= 'Select [date] ' + @S + ' ,SUM(value) As total From A Group By [date] Order By [date] '
EXEC(@S)
GO

Drop Table A
------解决方案--------------------

Declare @S Varchar(8000)
Select @S = 'Select id '
Select @S = @S + ', SUM(Case title When ' ' ' + title + ' ' ' Then value Else 0 End) As ' + title
From TableName Group By title
Select @S = @S + ' , SUM(value) As [sum] From TableName Group By id '
EXEC(@S)
------解决方案--------------------
if object_id( 'pubs..tb ') is not null
drop table tb
go

create table tb
(
id int,
title varchar(10),
value int,
date datetime
)

insert into tb(id,title,value,date) values(1, 'aaa ', 10, '2007-1-2 ')
insert into tb(id,title,value,date) values(1, 'ccc ', 2 , '2007-3-4 ')
insert into tb(id,title,value,date) values(2, 'ccc ', 4 , '2007-2-2 ')
insert into tb(id,title,value,date) values(1, 'ddd ', 3 , '2007-1-1 ')
insert into tb(id,title,value,date) values(2, 'ddd ', 3 , '2007-2-3 ')
insert into tb(id,title,value,date) values(2, 'aaa ', 11, '2007-4-2 ')

select id,
max(case when title = 'aaa ' then value end) as 'aaa ',
max(case when title = 'ccc ' then value end) as 'aaa ',
max(case when titl