将多行压缩为单行 - 要完成的select语句 [英] Compressing multiple rows into single row each- the select statement to accomplish

问题描述

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.

这篇关于将多行压缩为单行 - 要完成的select语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！