将多行压缩为单行

本文介绍了将多行压缩为单行 - 要完成的select语句的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

BRN_NAME MADD1 R1 R2 R3 R4 --------------------------------- ----------------------------------------- 阿散蒂区域1 31 NULL NULL NULL Ashanti Region 4 NULL NULL NULL 2 Ashanti Region 81 NULL NULL 5 NULL 大阿克拉地区1 29 NULL NULL NULL 大阿克拉地区4 NULL NULL NULL 2 大阿克拉地区81 NULL 7 NULL NULL Northern Region 1 20 NULL NULL NULL Northern Region 81 NULL 45 NULL NULL Northern Region 484 NULL NULL NULL 6 预期结果 ====== ============= BRN_NAME R1 R2 R3 R4 -------------------------------------------------- -------------------------------- Ashanti Region 31 NULL 5 2 大阿克拉地区29 7 NULL 2 北部地区20 45 NULL 6 每个'BRN_NAME'需要减少多行进入一个单一的行。 如何使用选择声明来实现它 我尝试过： 正在进行系统开发挑战

BRN_NAME MADD1 R1 R2 R3 R4 -------------------------------------------------------------------------- Ashanti Region 1 31 NULL NULL NULL Ashanti Region 4 NULL NULL NULL 2 Ashanti Region 81 NULL NULL 5 NULL Greater Accra Region 1 29 NULL NULL NULL Greater Accra Region 4 NULL NULL NULL 2 Greater Accra Region 81 NULL 7 NULL NULL Northern Region 1 20 NULL NULL NULL Northern Region 81 NULL 45 NULL NULL Northern Region 484 NULL NULL NULL 6 EXPECTED RESULT =================== BRN_NAME R1 R2 R3 R4 ---------------------------------------------------------------------------------- Ashanti Region 31 NULL 5 2 Greater Accra Region 29 7 NULL 2 Northern Region 20 45 NULL 6 Need to reduce multiple rows for each 'BRN_NAME' into a singular row. How do you achieve it using a select statement What I have tried: On going system development challenge

推荐答案

SELECT BRN_NAME, Max(R1) As R1, Max(R2) As R2, Max(R3) As R3, Max(R4) As R4 FROM YourTable GROUP BY BRN_NAME ;

NB：如果给定名称的一列中有多个非NULL值，则返回最高值。如果这不是您想要的，那么您需要解释如何决定保留哪个值。

NB: If there are multiple non-NULL values in one column for a given name, this will return the highest. If that's not what you want, then you'll need to explain how you decide which value to keep.