Create Table A ([date] Varchar(10), [quarter] Varchar(10), value Numeric(10,1)) 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
code 是编码 tp 是投票结果
Select @S=@S+',SUM(Case [quarter] When '''+ [quarter]+''' Then value Else 0 End) As ' 中 '''+ [quarter]+''' 是什么意思呀?
tp 有三种结果,即 1,2,3 tpt是总数
create table t(code int,tp int) insert into t select 1001,1 insert into t select 1002,2 insert into t select 1003,1 insert into t select 1001,1 insert into t select 1002,2 insert into t select 1003,3 declare @s varchar(8000) set @s='' select @s=@s+',sum(case when tp='+rtrim(tp)+' then 1 else 0 end) as tp'+rtrim(tp) from t group by tp select @s='select code'+@s+',count(tp) as tpt from t group by code' exec(@s)----result: code tp1 tp2 tp3 tpt ----------- ----------- ----------- ----------- ----------- 1001 2 0 0 2 1002 0 2 0 2 1003 1 0 1 2
declare @s varchar(8000) set @s='' select @s=@s+',sum(case when tp='+rtrim(tp)+' then 1 else 0 end) as tp'+rtrim(tp) from t group by tp select @s='select code'+@s+',count(tp) as tpt from t group by code' exec(@s)---這是動態的,tp有幾個類型,就會有幾個欄位,是行轉列比較普遍的寫法 --你可以加上print @s來看看語句到底是什麼意思. declare @s varchar(8000) set @s='' select @s=@s+',sum(case when tp='+rtrim(tp)+' then 1 else 0 end) as tp'+rtrim(tp) from t group by tp print @s ---加上select @s='select code'+@s+',count(tp) as tpt from t group by code' print @s ----加上 exec(@s)
create table test (code char(4), tp int)insert test(code,tp) select '1001',1 union all select '1002',2 union all select '1003',1 union all select '1001',1 union all select '1002',2 union all select '1003',3 select * from test go--用于:交叉表的列数是确定的 select code,sum(case tp when 1 then tp else 0 end) as 'tp1', sum(case tp when 2 then tp else 0 end) as 'tp2', sum(case tp when 3 then tp else 0 end) as 'tp3', sum(tp) as tpt from test group by codedrop table test
这个统计记录数的count ------------------------------------ create table test (code char(4), tp int)insert test(code,tp) select '1001',1 union all select '1002',2 union all select '1003',1 union all select '1001',1 union all select '1002',2 union all select '1003',3 select * from test go--用于:交叉表的列数是确定的 select code, tp1=(select count(1) from test where tp=1 and code =A.code), tp2=(select count(1) from test where tp=2 and code =A.code), tp3=(select count(1) from test where tp=3 and code =A.code), tpt=(select count(1) from test where code =A.code) from test A group by codedrop table test
([date] Varchar(10),
[quarter] Varchar(10),
value Numeric(10,1))
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
中
'''+ [quarter]+'''
是什么意思呀?
insert into t select 1001,1
insert into t select 1002,2
insert into t select 1003,1
insert into t select 1001,1
insert into t select 1002,2
insert into t select 1003,3
declare @s varchar(8000)
set @s=''
select @s=@s+',sum(case when tp='+rtrim(tp)+' then 1 else 0 end) as tp'+rtrim(tp) from t group by tp
select @s='select code'+@s+',count(tp) as tpt from t group by code'
exec(@s)----result:
code tp1 tp2 tp3 tpt
----------- ----------- ----------- ----------- -----------
1001 2 0 0 2
1002 0 2 0 2
1003 1 0 1 2
也就是说tp只能为值1、2、3如果是固定的就容易些~~~
set @s=''
select @s=@s+',sum(case when tp='+rtrim(tp)+' then 1 else 0 end) as tp'+rtrim(tp) from t group by tp
select @s='select code'+@s+',count(tp) as tpt from t group by code'
exec(@s)---這是動態的,tp有幾個類型,就會有幾個欄位,是行轉列比較普遍的寫法
--你可以加上print @s來看看語句到底是什麼意思.
declare @s varchar(8000)
set @s=''
select @s=@s+',sum(case when tp='+rtrim(tp)+' then 1 else 0 end) as tp'+rtrim(tp) from t group by tp
print @s ---加上select @s='select code'+@s+',count(tp) as tpt from t group by code'
print @s ----加上
exec(@s)
(code char(4),
tp int)insert test(code,tp)
select '1001',1 union all
select '1002',2 union all
select '1003',1 union all
select '1001',1 union all
select '1002',2 union all
select '1003',3
select * from test
go--用于:交叉表的列数是确定的
select code,sum(case tp when 1 then tp else 0 end) as 'tp1',
sum(case tp when 2 then tp else 0 end) as 'tp2',
sum(case tp when 3 then tp else 0 end) as 'tp3',
sum(tp) as tpt
from test
group by codedrop table test
------------------------------------
create table test
(code char(4),
tp int)insert test(code,tp)
select '1001',1 union all
select '1002',2 union all
select '1003',1 union all
select '1001',1 union all
select '1002',2 union all
select '1003',3
select * from test
go--用于:交叉表的列数是确定的
select code,
tp1=(select count(1) from test where tp=1 and code =A.code),
tp2=(select count(1) from test where tp=2 and code =A.code),
tp3=(select count(1) from test where tp=3 and code =A.code),
tpt=(select count(1) from test where code =A.code)
from test A
group by codedrop table test