Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL Query Merge Records 1

Status
Not open for further replies.

Herdrich

Technical User
Dec 30, 2008
83
US
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
SQL:
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
 
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:

Code:
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top