How to merge SQL rows with null values

Question

I have many rows for the same data but each row has a different columns populated. I want to join all these rows into a single row. I have tried group by, but the dataset has 1000 columns. Any suggestions?

SELECT TOP 100 * 
FROM [Hilltopsamplerfix].[dbo].[tempHilltopWaterQualityExtractPivot]
WHERE [SiteName] = 'site' AND [RunDate] = 'xxx'

Example of the data

I can't paste an image because my reputation is under 10

+------------------------------+
| Site column1 column2 column3 |
+------------------------------+
| SITE1 NULL   NULL    76      |
| SITE1 NULL   23      NULL    |
| SITE1 NULL   NULL    NULL    |
+------------------------------+

Desired output:

+------------------------------+
| Site column1 column2 column3 |
+------------------------------+
| SITE1 NULL   23      76      |
+------------------------------+

You need to at least show us your input and desired output.

Tim Biegeleisen
– Tim Biegeleisen

2015-08-19 02:38:46 +00:00
Commented Aug 19, 2015 at 2:38 — Tim Biegeleisen
– Tim Biegeleisen, Commented Aug 19, 2015 at 2:38

Gordon Linoff · Accepted Answer · 2015-08-19 02:39:19Z

2

You need to use group by:

select site, max(column1) as column1, max(column2) as column2, . . .
from [Hilltopsamplerfix].[dbo].[tempHilltopWaterQualityExtractPivot]
group by site;

You can get the columns in the table from information_schema.columns and construct the logic in the SQL or Excel. For example

select '    max(' + column_name + ') as ' + column_name + ', '
from information_schema.columns
where table_name = 'tempHilltopWaterQualityExtractPivot' and
      column_name <> 'site';

Then copy the results into the query window.

answered Aug 19, 2015 at 2:39

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

PythonGeo Over a year ago

Thanks Gordon, your help is appreciated. I tried the second script you provided. but the output is "(No column name)" I tried a few ways to make it work but was unable to.

Gordon Linoff Over a year ago

@PythonGeo . . . Can you edit your question and put in an example of the query you get?

PythonGeo Over a year ago

Hi Gordon, I was trying in python, so might not be appropriate to the SQL question.

selva kumar · Accepted Answer · 2015-08-19 07:53:29Z

1

Try this below SQL, Based on Gordon

Declare @Cols Varchar(Max)
select @Cols= coalesce(@Cols+',', '')+ ' max(' + column_name + ') as ' + column_name
from information_schema.columns
where table_name = 'tempHilltopWaterQualityExtractPivot' and Column_Name <> 'site'

Declare @Query Varchar(max)
SET @Query='Select Site, '+ @Cols+' From tempHilltopWaterQualityExtractPivot Group by Site'
EXEC(@Query)

answered Aug 19, 2015 at 7:53

selva kumar

1,1562 gold badges14 silver badges30 bronze badges

2 Comments

PythonGeo Over a year ago

Thanks Selva. But I only get "Command(s) completed successfully." in return. A table is not produced.

selva kumar Over a year ago

Hi Geo, Hope you are executed entire script at one shot without excluding EXEC statement especially. In my environment it is working fine and return maximum results from an table. If you removed the EXEC statement kindly include and test once again.

Collectives™ on Stack Overflow

How to merge SQL rows with null values

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related