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.
更多推荐
将多行压缩为单行
发布评论