I needed to fix a select, because I have the same id_question for different questions.

In the example below, id 297914 has 2 id_question with value '3' but the correct one would be '3' and '3A'

wrong way

correct way

Is there any way to fix this?

SQL Demo

CREATE TABLE #form 
(
  [id] int, 
  [id_question] varchar(2), 
  [answer] varchar(10)
 );

INSERT INTO #form
(
  [id], [id_question], [answer]
)
VALUES
    (297914, '3', 'aaa'),
    (297914, '3', 'bbb'),
    (297933, '3', 'ccc'),
    (297933, '3', 'ddd')


SELECT * FROM #form

CodePudding user response：

Would a simple flag to identify the first match work?

 select
    id, id_question,
    case when row_number() over(partition by id,id_question order by answer) = 1 then 1 else 0 END AS is_valid,
    answer      
FROM
    #form

CodePudding user response：

Try using a CTE with ROW_NUMBER to find the duplicate values, then UPDATE the table appending ASCII characters starting with @ 1, in which A follows @ (See ASCII table).

WITH CTE AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY id
                              ORDER BY id_question DESC, answer DESC) AS RN
    FROM #form)
UPDATE CTE SET id_question= id_question CHAR(ASCII('@')   1)
WHERE RN > 1;

See Fiddle

Result: