CREATE TABLE #temp (
CANDIDATEID int, SUB varchar(500), UBID INT,MARK INT,TOTAL_QUESTIONS int)
INSERT INTO #TEMP
values
(41,'IT',1,6,14),
(41,'MATH',2,3,10),
(41,'ENGLISH READING',3,4,10),
(41,'ENGLISH GRAMMER',4,3,15),
(42,'INFORMATION TECHNOLOGY',1,3,14),
(42,'MATH',2,2,10);
WITH CTE AS (
SELECT CANDIDATEID,CASE WHEN SUB IN ('INFORMATION TECHNOLOGY','IT') THEN 'IT' ELSE SUB END AS SUB,MARK FROM #TEMP
UNION
SELECT CANDIDATEID,'T_QNS_'+CASE WHEN SUB IN ('INFORMATION TECHNOLOGY','IT') THEN 'IT' ELSE SUB END AS SUB,TOTAL_QUESTIONS FROM #TEMP
)
select CANDIDATEID,[IT],[MATH],[ENGLISH READING], [ENGLISH GRAMMER],
[T_QNS_IT],[T_QNS_MATH],[T_QNS_ENGLISH GRAMMER],[T_QNS_ENGLISH READING]
from CTE
PIVOT
( MAX(MARK) FOR SUB IN (
[ENGLISH GRAMMER],[ENGLISH READING],[INFORMATION TECHNOLOGY],[IT],[MATH],
[T_QNS_ENGLISH GRAMMER],[T_QNS_ENGLISH READING],[T_QNS_INFORMATION TECHNOLOGY],[T_QNS_IT],[T_QNS_MATH]
)
) AS ff