sqlserver行列转换

一、使用sum(case when)

废话不多说,直接上图

select projectDeclare_id,
            sum(case when seq=0 then score1 else NULL end) as 'diyihang',
            sum(case when seq=1 then score1 else NULL end) as 'diyihang',
            sum(case when seq=2 then score1 else NULL end) as 'diyihang',
            sum(case when seq=3 then score1 else NULL end) as 'diyihang',
            sum(case when seq=4 then score1 else NULL end) as 'diyihang',
            sum(case when seq=5 then score1 else NULL end) as 'diyihang',
            sum(case when seq=6 then score1 else NULL end) as 'diyihang',
            sum(case when seq=7 then score1 else NULL end) as 'diyihang',
            sum(case when seq=8 then score1 else NULL end) as 'diyihang',
            sum(case when seq=9 then score1 else NULL end) as 'diyihang',
            sum(case when seq=10 then score1 else NULL end) as 'diyihang',
            sum(case when seq=11 then score1 else NULL end) as 'diyihang',
            sum(case when seq=12 then score1 else NULL end) as 'diyihang',
            sum(case when seq=13 then score1 else NULL end) as 'dierhang'
from (
SELECT
    * 
FROM
    Lab_Performance lp 
WHERE
    lp.projectDeclare_id IN (
SELECT
    pd.id 
FROM
    Pro_Declare pd
    LEFT JOIN pro_main pm ON pm.id = pd.projectMain_id
    LEFT JOIN Pro_Plan pp ON pp.id = pm.projectPlan_id 
WHERE
    pp.projectname LIKE '%孵化器统计绩效%' 
    AND pd.uniCode LIKE '%绩效_2022_二季度%' 
    )
) as b group by projectDeclare_id

需要用到行列转换的表一般都会用到三个字段

1、表中表示同一组数据的字段，我这里就是declare_id

2、需要分列的字段数据，就是转换成行之后的字段，我这里用的是seq

3、不同seq对应的数据，这个可以有很多个，多用几个case when 就行

sum(case when seq='?' then score else NULL end) as '字段名'

by the way，这里面select ..from(select..) as b这里需要有一个as 可能是子查询需要命名吧