sql: remove multiple row null values, concathenate into one row

Question

Lets say i have a table like this:

    col1     col2     col3
row1  1       null    null

row2  null    2       null

row3  null    null    3

All data types are numbers, integers or long. And i need a table like this:

    col1     col2     col3
row1  1       2        3

How can i accomplish this with an sql statement? I assume i have to create a new table and insert a new row with a select statement, but i am more of a coder and not sure what kind of statement i should use. Thanks for the help.

Why/How would you combine row1/2/3 into a single row? How do you identify that they should be grouped together? — Derek
– Derek, Commented Aug 12, 2011 at 15:17
How to merge such rows: |1|NULL|2 and |3|NULL|4, Just sum values? — sll
– sll, Commented Aug 12, 2011 at 15:18
You need more information; are you trying to collapse the entire table into one row? If not, how are you defining the subset of rows that will make up one row? Either way, how do you determine what value will go into, say, col if more than one row has a value? Do you want the max? min? sum? etc. — Adam Robinson
– Adam Robinson, Commented Aug 12, 2011 at 15:18
Do you only have those three rows that needs to be joined to one row or are there more rows that needs to be joined as well? — Mikael Eriksson
– Mikael Eriksson, Commented Aug 12, 2011 at 15:19

sjngm · Accepted Answer · 2011-08-16 07:01:00Z

sllev got me on the right track. In my case, because all values except for one were always going to be null in any given column, i just had to use the MAX() function. my final sql statement looked like this:

Select MAX(Merge_Table_Parcel_1.txtFrequency1) AS Frequency1,
       MAX(Merge_Table_Parcel_1.SUM_INST_N) AS SUM_INST_N1,
       MAX(Merge_Table_Parcel_1.SUM_INST_D) AS SUM_INST_D1,
       MAX(Merge_Table_Parcel_1.SUM_CTRN_D) AS SUM_CTRN_D1,
       MAX(Merge_Table_Parcel_1.SUM_CTRN_N) AS SUM_CTRN_N1,
       MAX(Merge_Table_Parcel_1.SUM_TRAN_N) AS SUM_TRAN_N1,
       MAX(Merge_Table_Parcel_1.SUM_PPU) AS SUM_PPU1,
       MAX(Merge_Table_Parcel_1.SUM_PPUJOBS) AS SUM_PPUJOBS1,
       MAX(Merge_Table_Parcel_1.SUM_DAYT) AS SUM_DAYT1,
       MAX(Merge_Table_Parcel_1.SUM_RESD_D) AS SUM_RESD_D1,
       MAX(Merge_Table_Parcel_1.SUM_RESD_N) AS SUM_RESD_N1,
       MAX(Merge_Table_Parcel_1.SUM_ON_STRN_D) AS SUM_ON_STRN_D1,
       MAX(Merge_Table_Parcel_1.SUM_ON_STRN_N) AS SUM_ON_STRN_N1
  FROM Merge_Table_Parcel_1

Simple, i know, but i dident see it right away. Thank you to everyone who contributed a response, and to sllev who pointed me in the right direction.

Joel Prabhu · Accepted Answer · 2015-03-07 07:10:33Z

0

SELECT MAX(col1) as Col1,
MAX(col2) as Col2,
MAX(col3) as Col3
FROM table_Name

i think it will help....

edited Mar 7, 2015 at 7:10

answered Mar 3, 2015 at 12:59

Joel Prabhu

11 silver badge5 bronze badges

Collectives™ on Stack Overflow

sql: remove multiple row null values, concathenate into one row

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related