use tempdb; /* create table t1 ( Name nvarchar(10) not null, ParaName nvarchar(10) not null, Value int not null ); insert into t1(Name,ParaName,Value) values ('0/0','C1',10), ('0/0','C2',10), ('0/0','C3',10), ('0/0','C4',5), ('0/0','R1',5), ('0/0','R2',5), ('1/1','C1',10), ('1/1','C2',10), ('1/1','C3',10), ('1/1','C4',5), ('1/1','R1',5), ('1/1','R2',5); */ select Name, SUM(case when ParaName = 'C1' then value end) as [C1], SUM(case when ParaName = 'C2' then value end) as [C2], SUM(case when ParaName = 'C3' then value end) as [C3], SUM(case when ParaName = 'C4' then value end) as [C4], SUM(case when ParaName = 'R1' then value end) as [R1], SUM(case when ParaName = 'R2' then value end) as [R2] from t1 group by Name;
use tempdb;
/*
create table t1
(
Name nvarchar(10) not null,
ParaName nvarchar(10) not null,
Value int not null
);
insert into t1(Name,ParaName,Value)
values
('0/0','C1',10),
('0/0','C2',10),
('0/0','C3',10),
('0/0','C4',5),
('0/0','R1',5),
('0/0','R2',5),
('1/1','C1',10),
('1/1','C2',10),
('1/1','C3',10),
('1/1','C4',5),
('1/1','R1',5),
('1/1','R2',5);
*/
select Name,
SUM(case when ParaName = 'C1' then value end) as [C1],
SUM(case when ParaName = 'C2' then value end) as [C2],
SUM(case when ParaName = 'C3' then value end) as [C3],
SUM(case when ParaName = 'C4' then value end) as [C4],
SUM(case when ParaName = 'R1' then value end) as [R1],
SUM(case when ParaName = 'R2' then value end) as [R2]
from t1
group by Name;
还得回家补习补习,刚才试试了1楼的方法,没问题。
其实我也查过二楼的经典,不过可能写法不对,老是出来NULL字段,聚合函数的实例也不太清楚原理!