SQLTeam.com | Weblogs | Forums

SQL Dynamic columns via Pivot


#1

Some one can suggest how to get the required out via pivot

Platform Testgroup testcase status

Platform1 TestcaseGroup1 T1 Passed
Platform1 TestcaseGroup1 T3 Passed
Platform2 TestcaseGroup1 T2 Failed
Platform2 TestcaseGroup1 T3 Passed

Testgroup Platform1 Platform2

TestcaseGroup1 2/0 0/1
(Passed/Failed)

Issue: Tried with Pivot .But Pivot is required the aggregate function.


#2
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)