计算列的元素个数

【高分】计算列的元素个数
数据库每一个单元格只有0，1，2的一个数 
把他们添加都数据库里面是这样的 
ID lie1 lie2 lie3 
1 2 1 1  
2 0 1 0  
3 0 1 1  
4 2 1 1  
5 2 2 1  
6 1 1 0  
。。。。。。 

我想把数据库里面每一列的0 ，1，2 统计出来，怎么做呢？ 
比如说得到lie1有2个0，1个1，3个2
  lie2有 0个0，5个1，1个2
  ..................
请详细说出步骤，我对行列互换一点不懂

[解决办法]

SQL code

create table T(ID int identity(1,1),lie1 int,lie2 int,lie3 int)insert into T select 2,1,0 union all select       0,1,1 union all select     0,2,1 union all select    1,1,2 union all select 0,0,1 union all select 1,2,2 declare @s1 nvarchar(4000);declare @s2 nvarchar(4000);declare @s3 nvarchar(4000);set @s1='标题=''lie1'''set @s2='''lie2'''set @s3='''lie3'''Select @s1=@s1+','+quotename(lie1)+'=sum(case when [lie1]='+quotename([lie1],'''')+' then 1 else 0 end)' from T group by [lie1]Select @s2=@s2+','+'sum(case when [lie2]='+quotename([lie2],'''')+' then 1 else 0 end)' from T group by [lie2]Select @s3=@s3+','+'sum(case when [lie3]='+quotename([lie3],'''')+' then 1 else 0 end)' from T group by [lie3]exec('select '+@s1+' from T union all select '+@s2+' from T union all select'+@s3+' from T')drop table T/*标题   0           1           2           ---- ----------- ----------- ----------- lie1 3           2           1lie2 1           3           2lie3 1           3           2*/
[解决办法]
探讨

SQL codecreate table T(ID int identity(1,1),lie1 int,lie2 int,lie3 int)
insert into T select 
2,1,0 union all select       
0,1,1 union all select     
0,2,1 union all select    
1,1,2 union all select 
0,0,1 union all select 
1,2,2 


declare @s1 nvarchar(4000);
declare @s2 nvarchar(4000);
declare @s3 nvarchar(4000);
set @s1='标题=''lie1'''
set @s2='''lie2'''
set @s3='''lie3'''
…

[解决办法]
有关CASE关键字的请看
SQL code
CASE计算条件列表并返回多个可能结果表达式之一。 CASE 具有两种格式： 简单 CASE 函数将某个表达式与一组简单表达式进行比较以确定结果。 CASE 搜索函数计算一组布尔表达式以确定结果。 两种格式都支持可选的 ELSE 参数。 语法简单 CASE 函数：CASE input_expression    WHEN when_expression THEN result_expression        [ ...n ]    [         ELSE else_result_expression    END
[解决办法]
探讨

insert into T select 
2,1,0 union all select      
0,1,1 union all select    
0,2,1 union all select    
1,1,2 union all select 
0,0,1 union all select 
1,2,2 

这个什么意思呢？

[解决办法]
建议你直接看SQL SERVER联机丛书 那是最好的教材
[解决办法]
探讨

不解释一下，我不懂都不给分！！！

[解决办法]
SQL code
create table T(ID int identity(1,1),lie1 int,lie2 int,lie3 int)insert into T select 2,1,0 union all select       0,1,1 union all select     0,2,1 union all select    1,1,2 union all select 0,0,1 union all select 1,2,2 --SELECT * FROM TSELECT LIE='LIE1' ,SUM(CASE WHEN LIE1=0 THEN 1 ELSE 0 END)AS '0',SUM(CASE WHEN LIE1=1 THEN 1 ELSE 0 END)AS '1',SUM(CASE WHEN LIE1=2 THEN 1 ELSE 0 END)AS '2'FROM TUNION ALLSELECT LIE='LIE2' ,SUM(CASE WHEN LIE2=0 THEN 1 ELSE 0 END)AS '0',SUM(CASE WHEN LIE2=1 THEN 1 ELSE 0 END)AS '1',SUM(CASE WHEN LIE2=2 THEN 1 ELSE 0 END)AS '2'FROM TUNION ALLSELECT LIE='LIE3' ,SUM(CASE WHEN LIE3=0 THEN 1 ELSE 0 END)AS '0',SUM(CASE WHEN LIE3=1 THEN 1 ELSE 0 END)AS '1',SUM(CASE WHEN LIE3=2 THEN 1 ELSE 0 END)AS '2'FROM T 
 
[解决办法]
探讨

SQL code
create table T(ID int identity(1,1),lie1 int,lie2 int,lie3 int)
insert into T select 
2,1,0 union all select       
0,1,1 union all select     
0,2,1 union all select    
1,1,2 union all select 
0,0,1 union all select 
1,2,2 

--SELECT * FROM T

SELECT LIE='LIE1' ,
SUM(CASE WHEN LIE1=0 THEN 1 ELSE 0 END)AS '0',
SUM(CASE WHEN LIE1=1 THEN 1 ELSE 0 END)AS '1',
SUM(CASE WHEN …

[解决办法]
探讨

Select @s1=@s1+','+quotename(lie1)+'=sum(case when [lie1]='+quotename([lie1],'''')+' then 1 else 0 end)' from T group by [lie1] 
Select @s2=@s2+','+'sum(case when [lie2]='+quotename([lie2],'''')+' then 1 else 0 end)' from T group by [lie2] 
Select @s3=@s3+','+'sum(case when [lie3]='+quotename([lie3],'''')+' then 1 else 0 end)' from T group by [lie3] 

这个什么意思呢？？

[解决办法]
探讨

insert into T select 
2,1,0 union all select      
0,1,1 union all select    
0,2,1 union all select    
1,1,2 union all select 
0,0,1 union all select 
1,2,2 

这个什么意思呢？

[解决办法]
select liename,
[value],
num = count([value])
from
(select id,lie1,lie2,lie3 from #temp) t1
unpivot
( [value] for [liename] in (lie1,lie2,lie3)) t2
group by liename,[value]
order by liename,[value]
[解决办法]
就看懂一部分 哎
[解决办法]
ds.tables(0).column.count