行转列,照抄一下给你~~~Create Table A
(
[date] Varchar(10),
[quarter] Varchar(10),
[value] Numeric(10,1)
)
GOInsert 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
GODeclare @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)
GODrop Table A
(
[date] Varchar(10),
[quarter] Varchar(10),
[value] Numeric(10,1)
)
GOInsert 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
GODeclare @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)
GODrop 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)
drop table tb
gocreate 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 iddrop table tbid aaa aaa aaa sum
----------- ----------- ----------- ----------- -----------
1 10 2 3 15
2 11 4 3 18(所影响的行数为 2 行)
(id Int,
title Varchar(10),
value Int,
[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
/*
id aaa ccc ddd sum
1 10 2 3 15
2 11 4 3 18
*/
if object_id('pubs..tb') is not null
drop table tb
gocreate 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 tbid aaa ccc ddd sum
----------- ----------- ----------- ----------- -----------
1 10 2 3 15
2 11 4 3 18
我不知道title的数量呀应该怎么办?
---------沒有影響的,我們寫的代碼就是在title的数量不固定下應用的。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)
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 where id>1 Group By id'
EXEC(@S)可是什么都查不出来。。这个是为什么?
create table TEST
(id Int,
title Varchar(10),
value Int,
date Datetime
)Insert Into TEST Select 1, 'aaa', 10, '2007-1-2'
Insert Into TEST Select 1, 'ccc', 2 , '2007-3-4'
Insert Into TEST Select 2, 'ccc', 4 , '2007-2-2'
Insert Into TEST Select 1, 'ddd', 3 , '2007-1-1'
Insert Into TEST Select 2, 'ddd', 3 , '2007-2-3'
Insert Into TEST 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 where id>1 Group By id'
EXEC(@S)
GO
Drop Table TEST
--Result
/*
id aaa ccc ddd sum
2 11 4 3 18
*/
dawugui(潇洒老乌龟) ( 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 where id>1 group by id'
exec(@sql)