CODEWITHSUNDEEP
sqlsql-server
SQLserving
SQLserving

Reputation: 400

Merge rows into one by changing column value

I have Table like this:

Students: 

ID|NAME  |FOOD |Score  
1 |FRED  |Apple|23  
1 |FRED  |Pear |50  
2 |HENRY |BERRY|20  
3 |Alex  |Apple|40

What I want to do is to change the FOOD to 'MIXED' for all students eat more than 1 different FOOD. So the result would look like this: 

ID|NAME  |FOOD |Score   
1 |FRED  |MIXED|73  
2 |HENRY |BERRY|20  
3 |Alex  |Apple|40

Any ideas?

Upvotes: 0

Views: 89

Answers (3)

potashin
potashin

Reputation: 44601

You can use group by and iif with count condition:

select [ID], 
       [NAME], 
       iif(count([FOOD]) > 1, 'MIXED', [FOOD]) as [FOOD], 
       sum([Score]) as [Score]
from [Students] 
group by [ID], 
         [NAME]

You should use count(distinct [FOOD]) in case FOOD entries can be repeated for the same ID and NAME.

Upvotes: 1

SQLChao
SQLChao

Reputation: 7847

Possible solution using case

DEMO

   SELECT
        id
      , name
      , CASE WHEN COUNT(1) > 1 THEN 'MIXED' ELSE MAX(FOOD) END AS Food
      , SUM(Score) AS Score
    FROM dbo.students
    GROUP BY id, name
    ORDER BY id

Upvotes: 1

Grantly
Grantly

Reputation: 2556

Using GROUP BY could get want you want. This is a neat little trick to see if 'different values' are in more than one row

Try this on your data

SELECT
  ID
  ,NAME
  ,CASE 
        WHEN MIN(FOOD) <> MAX(FOOD) 
        THEN 'MIXED' ELSE [FOOD] 
   END AS FOOD
  ,SUM(Score) AS Score
GROUP BY 
  ID
  ,NAME

Upvotes: 1

Related Questions