高手帮忙,数据统计问题

[powbcom]

表数据字段A,B,C,D 四个字段
每个字段存储的值为2、1、0、-1、2中其中一个，要求统计每个数字在A，B,C,D中的数量，
结果如下：
A B C D
8 6 8 8
12 5 6 54
54 23 23 34
4 7 78 45
12 45 47 78

先谢谢了

[子陌红尘]

select
b.id,
sum(case A when b.id then 1 else 0 end) as A,
sum(case B when b.id then 1 else 0 end) as B,
sum(case C when b.id then 1 else 0 end) as C,
sum(case D when b.id then 1 else 0 end) as D
from
表 a,
(select 2 as id union select 1 union select 0 union select -1 union select -2) b
group by
b.id

[中国风]

楼主是要统计ABCD列有以下数据的列?
2、1、0、-1、2

[子陌红尘]

declare @t table(A int,B int,C int,D int)
insert into @t select 1, 2, 0,-1
insert into @t select -2, 1,-1, 2
insert into @t select 1,-2, 2, 1
insert into @t select 0, 2, 0, 0
insert into @t select 2,-1,-2,-2
insert into @t select -1, 0, 1,-1

select
b.id,
sum(case A when b.id then 1 else 0 end) as A,
sum(case B when b.id then 1 else 0 end) as B,
sum(case C when b.id then 1 else 0 end) as C,
sum(case D when b.id then 1 else 0 end) as D
from
@t a,
(select 2 as id union select 1 union select 0 union select -1 union select -2) b
group by
b.id

/*
id A B C D
----------- ----------- ----------- ----------- -----------
-2 1 1 1 1
-1 1 1 1 2
0 1 1 2 1
1 2 1 1 1
2 1 2 1 1
*/

[dawugui]

declare @t table(a int , b int , c int , d int)
insert @t
select 0,0,0,0 union all
select 1,1,1,1 union all
select 2,2,2,2 union all
select -1,-1,-1,-1 union all
select -1,-1,-1,-1 union all
select -2,-2,-2,-2

declare @tb table(id int)
insert @tb
select 0 union all
select 1 union all
select 2 union all
select -1 union all
select -2

select
b.id,
sum(case A when 0 then 1 else 0 end) as A,
sum(case B when 1 then 1 else 0 end) as B,
sum(case C when 2 then 1 else 0 end) as C,
sum(case D when -1 then 1 else 0 end) as D
from @t ,
(select 2 as id union select 1 union select 0 union select -1 union select -2) b
group by b.id



id A B C D
----------- ----------- ----------- ----------- -----------
-2 1 1 1 2
-1 1 1 1 2
0 1 1 1 2
1 1 1 1 2
2 1 1 1 2

（所影响的行数为 5 行）

[dawugui]

上面错了.

declare @t table(a int , b int , c int , d int)
insert @t
select 0,0,0,0 union all
select 1,1,1,1 union all
select 2,2,2,2 union all
select -1,-1,-1,-1 union all
select -1,-1,-1,-1 union all
select -2,-2,-2,-2

declare @tb table(id int)
insert @tb
select 0 union all
select 1 union all
select 2 union all
select -1 union all
select -2

select
b.id,
sum(case A when b.id then 1 else 0 end) as A,
sum(case B when b.id then 1 else 0 end) as B,
sum(case C when b.id then 1 else 0 end) as C,
sum(case D when b.id then 1 else 0 end) as D
from @t ,
(select 2 as id union select 1 union select 0 union select -1 union select -2) b
group by b.id



id A B C D
----------- ----------- ----------- ----------- -----------
-2 1 1 1 1
-1 2 2 2 2
0 1 1 1 1
1 1 1 1 1
2 1 1 1 1

（所影响的行数为 5 行）

[dawugui]

declare @t table(a int , b int , c int , d int)
insert @t
select 0,0,0,0 union all
select 1,1,1,1 union all
select 2,2,2,2 union all
select -1,-1,-1,-1 union all
select -1,-1,-1,-1 union all
select -2,-2,-2,-2

declare @tb table(id int)
insert @tb
select 0 union all
select 1 union all
select 2 union all
select -1 union all
select -2

select
b.id,
sum(case A when b.id then 1 else 0 end) as A,
sum(case B when b.id then 1 else 0 end) as B,
sum(case C when b.id then 1 else 0 end) as C,
sum(case D when b.id then 1 else 0 end) as D
from @t ,
(select * from @tb) b
group by b.id



id A B C D
----------- ----------- ----------- ----------- -----------
-2 1 1 1 1
-1 2 2 2 2
0 1 1 1 1
1 1 1 1 1
2 1 1 1 1

（所影响的行数为 5 行）

[中国风]

create table roy(A int,B int,C int,D int)
insert into roy select 1, 2, 0,-1
insert into roy select -2, 1,-1, 2
insert into roy select 1,-2, 2, 1
insert into roy select 0, 2, 0, 0
insert into roy select 2,-1,-2,-2
insert into roy select -1, 0, 1,-1

楼主结贴了,那我也回复一个动态的写法
declare @sql varchar(1000)
set @sql=''
select @sql=@sql+',['+cast(name as varchar)+']=sum(case '+name +' when b.id then 1 else 0 end)'
from syscolumns where id=object_id('roy')
set @sql= 'select b.id '+@sql+
' from roy,(select 2 as id union select 1 union select 0 union select -1 union select -2) b group by b.id'
exec(@sql)
id A B C D
----------- ----------- ----------- ----------- -----------
-2 1 1 1 1
-1 1 1 1 2
0 1 1 2 1
1 2 1 1 1
2 1 2 1 1