请教一个查询SQL语句

[Faramitav]

有一个表A,字段如下
id s1 s2 tp
1 10 15 1
2 54 23 1
3 23 45 2

现在想按照tp来分类汇总S1和S2，而且要在同一行
例如查询后结果应该是这样的
相当于以tp为条件，分别sum(s1),sum(s2)，然后显示到同一行上
ts1 ts2 ys1 ys2
64 38 23 25

[山那頭]

sql列转行，网上帖子多，百度看看；

[道玄希言]

只有两行变成一行，可以这么写。

;with tbl1(id,s1,s2,tp) as
(
  select 1,10,15,1 union all
  select 2,54,23,1 union all
  select 3,23,45,2
 ),
 tbl2 as
 (
   select tp, sum(s1) as s1,sum(s2) as s2 
   from tbl1
   group by tp
),
tbl3 as
(
  select 
    ROW_NUMBER() OVER(order by tp) as nid, 
    s1, s2 
  from tbl2
)
select top 1 
  t1.s1 as ts1, t1.s2 as ts2, 
  t2.s1 as ys1, t2.s2 as ys2  
from tbl3 as t1 
left join tbl3 as t2
on t1.nid = t2.nid -1

[道玄希言]

静态行转列：

;with tbl1(id,s1,s2,tp) as
(
  select 1,10,15,1 union all
  select 2,54,23,1 union all
  select 3,23,45,2
 ),
 tbl2 as
 (
   select tp, sum(s1) as s1,sum(s2) as s2 
   from tbl1
   group by tp
),
tbl3 as
(
  select 
    ROW_NUMBER() OVER(order by tp) as nid, 
    s1, s2, 1 as id 
  from tbl2
)

select 
  t1.[1] as ts1, t2.[1] as ts2,  
  t1.[2] as ys1, t2.[2] as ys1
from 
(
select id, [1],[2]
from (select id, nid, s1 from tbl3) as t1
pivot
  (
     avg(s1) for nid in([1],[2])
  ) as T
) as t1
inner join 
(
select id, [1],[2]
from (select id, nid, s2 from tbl3) as t1
pivot
  (
     avg(s2) for nid in([1],[2])
  ) as T
)as t2
on t1.id = t2.id

ts1	ts2	ys1	ys1
64	38	23	45

[Faramitav]

非常感谢~~