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