SQLServer行转列SQL语句完整示例

╰+哭是因爲堅強的太久メ 2024-02-05 19:56 74阅读 0赞

## 行转列示例 ##

原始数据：  
![在这里插入图片描述][fb10cea1a8c34714bf459ca335e22795.png]

使用`case when`之后，张三的课程分数分布于三行中，现在需要将其压平至一行：  
![在这里插入图片描述][ae224e70a0a44c4bb901c819befb2a9d.png]

使用`sum`聚合之后：  
![在这里插入图片描述][5122aec530434a01ad1b8d5eca09d5da.png]

最好还是加上`isnull`判断下：  
![在这里插入图片描述][30351c25847f4d39b6b3fc2a91ea30b4.png]

对比下：  
![在这里插入图片描述][7d49e992fe4b491cbcd9b7cd12bffdc3.png]

### 完整SQL ###

---------------------------行转列--------------------------------------------------------------------------------
    SELECT [name],
    isnull(sum(case course when '高级班' then score end),0) as '高级班',
    isnull(sum(case course when '架构班' then score end),0) as '架构班',
    isnull(sum(case course when '上位机班' then score end),0) as '上位机班',
    isnull(sum(case course when 'Web前端' then score end),0) as 'Web前端',
    isnull(sum(case course when '全栈班' then score end),0) as '全栈班'
    FROM [ZhaoxiEdu].[dbo].[ScoreInfo] group by [name]

[fb10cea1a8c34714bf459ca335e22795.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/563cc6b9299344768c545dae08376f73.png
[ae224e70a0a44c4bb901c819befb2a9d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/bf674d516e3740abbd2834ead56005f4.png
[5122aec530434a01ad1b8d5eca09d5da.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/18551cc161d64060b772e8ab6d03d4f5.png
[30351c25847f4d39b6b3fc2a91ea30b4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/28dec3cb980e4065bd43f96b5e5948f3.png
[7d49e992fe4b491cbcd9b7cd12bffdc3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/fbd9b0e4452d4c3d917979df9aef6856.png