This code will dynamically

- add a suffix to each duplicated QuestionNo

- build SQL to pivot the data

UPDATE dbo.TestTable1

SET Identifier = upd.Identifier

FROM dbo.TestTable1 AS orig

INNER JOIN (

SELECT

data.PKID

, Identifier = data.Identifier + sfx.suffix

FROM (

SELECT src.Identifier, src.PKID

, rn = ROW_NUMBER() OVER (PARTITION BY src.Identifier ORDER BY src.PKID)

FROM dbo.TestTable1 as src

INNER JOIN (SELECT Identifier FROM dbo.TestTable1

WHERE Identifier LIKE 'Question%'

GROUP BY Identifier

HAVING COUNT(*) > 1

) AS dup ON src.Identifier = dup.Identifier

) AS data

CROSS APPLY ( SELECT CASE WHEN data.rn <= 26 THEN CHAR(((data.rn -1) %26) + 97)

ELSE CHAR(((data.rn -1) /26) + 96) + CHAR(((data.rn -1) %26) + 97)

END

) AS sfx(suffix)

) AS upd ON orig.PKID = upd.PKID;

DECLARE @cols VARCHAR(MAX) = STUFF((SELECT ', ' + QUOTENAME(Identifier)

FROM dbo.TestTable1

ORDER BY CASE WHEN Identifier LIKE 'Question%' THEN 1 ELSE 0 END, PKID

FOR XML PATH('')

), 1, 2, '')

DECLARE @SQL VARCHAR(MAX) = '

select Period, Name, ' + @cols + '

from (select Period, data, Identifier

from dbo.TestTable1

) z

pivot ( max(data)

for Identifier in (' + @cols + ')

) piv

';