怎么让多列对应多列数据只显示一列,并汇总!表:A B C D
1 2 3 5
1 2 3 6
1 2 3 7
1 2 4 8
1 2 5 9
1 2 6 4要显示结果:
A B C D
1 2 3 5
3 6
3 7
4 8
5 9
6 4
汇总: 24 39
1 2 3 5
1 2 3 6
1 2 3 7
1 2 4 8
1 2 5 9
1 2 6 4要显示结果:
A B C D
1 2 3 5
3 6
3 7
4 8
5 9
6 4
汇总: 24 39
id int identity(1,1) not null primary key
, A int, B int, C int, D int)insert into @tb (A,B,C,D)
select 1, 2, 3, 5 union
select 1, 2, 3, 6 union
select 1, 2, 3, 7 union
select 1, 2, 4, 8 union
select 1, 2, 5, 9 union
select 1, 2, 6, 4select
nullif(a.A,b.A) A,
nullif(a.B,b.B) B,
nullif(a.C,b.C) C,
nullif(a.D,b.D) D
from @tb a
left join @tb b on b.id = a.id-1-- A B C D
-- 1 2 3 5
-- 6
-- 7
-- 4 8
-- 5 9
-- 6 4
--
(
a INT,
b INT,
c INT,
d INT
)
INSERT INTO t1
SELECT 1, 2, 3, 5 UNION ALL
SELECT 1, 2, 3, 6 UNION ALL
SELECT 1, 2, 3, 7 UNION ALL
SELECT 1, 2, 4, 8 UNION ALL
SELECT 1, 2, 5, 9 UNION ALL
SELECT 1, 2, 6, 4
SELECT * FROM t1;WITH aaa AS
(
SELECT ROW_NUMBER() OVER(PARTITION BY a,b ORDER BY GETDATE()) AS rowindex,* FROM t1
)
,bbb AS
(
SELECT rowindex,LTRIM(a) AS a,LTRIM(b) AS b,c,d FROM aaa WHERE rowindex=1
UNION
SELECT rowindex,'','',c,d FROM aaa WHERE rowindex>1
UNION
SELECT MAX(rowindex)+1 AS rowindex,'汇总',':',SUM(c) AS c,SUM(d) AS d FROM aaa GROUP BY a,b
)
SELECT a,b,c,d FROM bbb ORDER BY rowindex----------------------
a b c d
1 2 3 5
3 6
3 7
4 8
5 9
6 4
汇总 : 24 39