您当前的位置: 有一个表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
------解决思路----------------------
只有两行变成一行，可以这么写。
;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