求一个复杂的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 title = 'ddd ' then value end) as 'aaa ',
sum(value) as sum
from tb
group by id
drop table tb
id aaa aaa aaa sum
----------- ----------- ----------- ----------- -----------
1 10 2 3 15
2 11 4 3 18
（所影响的行数为 2 行）
[解决办法]
Create Table TEST
(idInt,
titleVarchar(10),
valueInt,
[date]Varchar(10))
Insert TEST Select 1, 'aaa ', 10, '2007-1-2 '
Union All Select 1, 'ccc ', 2, '2007-3-4 '
Union All Select 2, 'ccc ', 4, '2007-2-2 '
Union All Select 1, 'ddd ', 3, '2007-1-1 '
Union All Select 2, 'ddd ', 3, '2007-2-3 '
Union All Select 2, 'aaa ', 11, '2007-4-2 '
GO
Declare @S Varchar(8000)
Select @S = 'Select id '
Select @S = @S + ', SUM(Case title When ' ' ' + title + ' ' ' Then value Else 0 End) As ' + title
From TEST Group By title
Select @S = @S + ' , SUM(value) As [sum] From TEST Group By id '
EXEC(@S)
GO
Drop Table TEST
--Result
/*
idaaacccdddsum
1102315
2114318
*/
[解决办法]
上面为静态SQL,以下为动态SQL
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 ')
go
declare @sql varchar(8000)
set @sql = 'select id '
select @sql = @sql + ' , max(case title when ' ' ' + title + ' ' ' then value else 0 end) [ ' + title + '] '
from (select distinct title from tb) as a
set @sql = @sql + ' ,sum(value) as sum from tb group by id '
exec(@sql)
drop table tb
id aaa ccc ddd sum
----------- ----------- ----------- ----------- -----------
1 10 2 3 15
2 11 4 3 18
