Add values of two rows if certain column values match

[jase2006]

I have a table which contains LoginID, WC, ProjectID, Mon, Tues, Wed, ......,Sun. More than one row can have the same LoginID, WC and ProjectID and if that is the case then I want to merge these rows' value together. Can anyone show me a sample code of doing this? The Values in Mon - Sun column is either 1 or 0.

 

[LKBrwnDBA]

Try MAX() or SUM() function:

Select LoginID, WC, ProjectID
     , MAX(Mon) Mon
     , MAX(Tue) Tues
     , MAX(Wed) Wed
     , ......
     , MAX(Sun) Sun
  From MyTable
 Group By LoginID, WC, ProjectID;

-- Or --

Select LoginID, WC, ProjectID
     , SUM(Mon) Mon
     , SUM(Tue) Tues
     , SUM(Wed) Wed
     , ......
     , SUM(Sun) Sun
  From MyTable
 Group By LoginID, WC, ProjectID;

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[jase2006]

It's not seem to be work, I actually forgot to mention "comment" field which contain a random txt. Would that cause a problem?

 

[LKBrwnDBA]

Use MAX(comment).


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[jase2006]

Thanks LKBrwnDBA, it's working now. Another question came up, what if I want to concatenate the "comments" together in the new merge row. Is that possible?

 

[AlexCuse]

I don't believe there is an ansi solution to that problem.

What database are you using, because there almost surely is a solution in that particular SQL Dialect.

[small]----signature below----[/small]
With all due respect, Don Bot, I don't think we should rely on an accident happening. Let's kill him ourselves.

Ignorance of certain subjects is a great part of wisdom

 

[jase2006]

I'm using SQL SERVER 2000.

 

[AlexCuse]

Check out this thread: thread183-1159740

Hope it helps,

Alex

[small]----signature below----[/small]
With all due respect, Don Bot, I don't think we should rely on an accident happening. Let's kill him ourselves.

Ignorance of certain subjects is a great part of wisdom