sql 语句 竖表转横表再加连接查询（如下图），求sql 语句

Question

sql 语句 竖表转横表再加连接查询（如下图），求sql 语句

Answer 1

select a.reportid,a.reportdate,a.person,a.remark,
sum((case b.testitem when '项目1' then b.testvalue end)) "项目1",
sum((case b.testitem when '项目2' then b.testvalue end)) "项目2",
sum((case b.testitem when '项目3' then b.testvalue end)) "项目3",
sum((case b.testitem when '项目4' then b.testvalue end)) "项目4",
sum((case b.testitem when '项目5' then b.testvalue end)) "项目5",
sum((case b.testitem when '项目6' then b.testvalue end)) "项目6",
sum((case b.testitem when '项目7' then b.testvalue end)) "项目7"
from table_a a
left join table_b b
on a.reportid=b.reportid
group by a.reportid,a.reportdate,a.person,a.remark
order by a.reportid