IF OBJECT_ID('tempdb..#temp') IS NOT NULL
DROP TABLE #temp
create table #temp
(
[Platform] varchar(20),
Testgroup varchar(20),
testcase varchar(20),
Status varchar(20))
insert into #temp values
('Platform1','TestcaseGroup1','T3','Passed')
,('Platform2','TestcaseGroup1','T2','Failed')
,('Platform2','TestcaseGroup1','T3','Passed')
,('Platform1','TestcaseGroup1','T1','Passed')
,('Platform2','TestcaseGroup2','T2','Failed')
,('Platform2','TestcaseGroup3','T3','Passed')
,('Platform1','TestcaseGroup3','T1','Passed')
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX);
SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.[Platform])
FROM #temp c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = ';with cte as (
select [Platform], testGroup,
cast(sum(case when status = ''Passed'' then 1 else 0 End) as varchar(10)) + ''/'' + cast(sum(case when status = ''Failed'' then 1 else 0 End) as varchar(10)) as PassFail
from #Temp
group by [Platform], testGroup)
SELECT * from
(
select testGroup
, [Platform]
, PassFail
from cte
) x
Pivot ( max(PassFail) for [Platform] in (' + @cols + ')
) p '
execute(@query)