SQL Query Merge Records 1

[Herdrich]

How to I merge multiple rows of data from the same table with the same ID. I have tried group by by I get the following error Column 'cos_attribute.bunit' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

The running this query

select * FROM cos_attribute WHERE AssetID = 14

provides a result of

AssetID bunit system_type priority
14 Personnel Services NULL NULL
14 NULL Printer NULL
14 NULL NULL Low

What I would like to see is
AssetID bunit system_type priority
14 Personnel Services Printer Low

 

[TamarGranor]

If the issue is that somehow you've got multiple records with the same ID, and each has data for one or more columns and nulls for the others, then you can apply the MAX() aggregation function to each column. In this example:

SELECT AssetID, MAX(bunit), MAX(system_type), MAX(priority) 
  FROM cos_attribute 
  WHERE AssetID = 14 
  GROUP BY AssetID

More broadly, when you use GROUP BY, every column in the query must either be in the GROUP BY list or use one of the aggregate functions. Your example makes it clear exactly why that is: because otherwise how would the query know which value from that column to pick up for the result.

Tamar

 

[Herdrich]

Thank you!!!!!!