Avoid duplicates rows which have one different column value in SQL Server

I have a view like this . I’m using SQL Server.

formId    name    mark
100       Rob      N
101       Rob      N
101       Rob      C
105       Jane     N
106       Jane     N

view name: view_example

There, some formId’s have two kinds of mark values as N and C. and some have single values as N. I want to print this to a report if formid is duplicating with different mark values, print the raw with mark value C . If form id is not duplicating print the value as it is. out put should look like this

formId    name   mark
 100      Rob     N
 101      Rob     C
 105      Jane    N
 106      Jane    N

I want to write the SQL query for this.

Answer

You could GROUP BY all the columns where you want to preserve all the values, and apply an aggregate function (in this case – MIN, in order to give C precedence over N) on the mark column:

SELECT   formId, name, MIN(mark) AS mark
FROM     my_table
GROUP BY formId, name

Advertisement

Answer