这样的分组怎么实现？

[yooono]

现有表
id lx value
1 a 10
2 b 20
3 b 11
4 a 15
5 a 16
生成查询
如果lx为a则表示一班，b表示2班,值是所有lx为a的和
一班 二版
（10+15+16） （20+11）

[skyzcl]

select lx,sum(value) from tb group by lx

[chuifengde]

select 
一班=sum(case when lx='a' then value else 0 end ),
二班=sum(case when lx='b' then value else 0 end )
from [Table] group by lx

[gutlgutl]

[Quote=引用 1 楼 skyzcl 的回复:]
SQL codeselect lx,sum(value) from tb group by lx
[/Quote]

[Limpire]

--> 横向
select
	一班=sum(case lx when 'a' then value else 0 end),
	二版=sum(case lx when 'b' then value else 0 end)
from table_name
group by table_name

--> 纵向
select lx, sum(value) from table_name group by lx

[hery2002]

 
declare @tb table ([id] int,[lx] nvarchar(1),[value] nvarchar(10))
Insert into @tb
 select 1,'a',10 union all 
 select 2,'b',20 union all 
 select 3,'b',11 union all 
 select 4,'a',15 union all 
 select 5,'a',16
  
--Select * from @tb
SELECT [lx],
           MAX( CASE seq WHEN 1 THEN [value] ELSE '0' END ) + '+' +
           MAX( CASE seq WHEN 2 THEN [value] ELSE '0' END ) + '+' +
           --MAX( CASE seq WHEN 3 THEN [value] ELSE '' END ) + '+ ' +
           MAX( CASE seq WHEN 3 THEN [value] ELSE '0' END ) as expression
      FROM ( SELECT p1.[lx], p1.[value],
                    ( SELECT COUNT(*) 
                        FROM @tb p2
                       WHERE p2.[lx] = p1.[lx]
                         AND p2.[value] <= p1.[value] )
               FROM @tb p1 ) D ( [lx], [value], seq )
     GROUP BY [lx] ;
/*
lx   expression
---- --------------------------------
a    10+15+16
b    11+20+0

(2 row(s) affected)
*/

[yooono]

俄，我就是横向不会，没专业搞过sql，谢谢大家

[yooono]

SELECT 一班 = SUM(CASE WHEN lx = 'a' THEN val ELSE 0 END),
二班 = SUM(CASE WHEN lx = 'b' THEN val ELSE 0 END)
FROM test
最终是这么用的，只要一行
是不是这样效率比较慢呀