with dynamic values for StudentCodeDECLARE @columns NVARCHAR(MAX)		,@columnsFees NVARCHAR(MAX)		,@columnsAntet NVARCHAR(MAX)		,@sql NVARCHAR(MAX);SET @columns = N'';SET @columnsFees =N''SET @columnsAntet = N'';SELECT @columns += N', ' + QUOTENAME(Name)  FROM (SELECT S.studentCode AS Name FROM #tmpSample AS S		GROUP BY S.studentCode) AS x;SELECT @columnsFees += N', ' + QUOTENAME('F'+Name)  FROM (SELECT S.studentCode AS Name FROM #tmpSample AS S		GROUP BY S.studentCode) AS x;SELECT @columnsAntet += N', ' + QUOTENAME(Name) + 'AS student'+Name+'Code, '			+ QUOTENAME('F'+Name) + 'AS student'+Name+'Fees'  FROM (SELECT S.studentCode AS Name FROM #tmpSample AS S		GROUP BY S.studentCode) AS x;SET @sql = N'SELECT studentID ,' + STUFF(@columnsAntet, 1, 2, '') + 'FROM(  SELECT studentID,studentCode, ''F''+studentCode AS studentCodeFees , studentFees FROM #tmpSample) AS jPIVOT(  MAX(studentFees) FOR studentCodeFees IN ('  + STUFF(REPLACE(@columnsFees, ', p.[', ',['), 1, 1, '')  + ')) AS pPIVOT(  MAX(studentCode) FOR studentCode IN ('  + STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')  + ')) AS R;';PRINT @sql;EXEC sp_executesql @sql;result setstudentID	student04Code	student04Fees	student05Code	student05Fees100	04	10	NULL	NULL101	04	5	NULL	NULL102	04	10	NULL	NULL103	NULL	NULL	05	10104	NULL	NULL	05	10
sabinWeb MCP