Use Of PIVOT in SQL Server without aggregate function

Question

I want to change rows into column as it is.. means without any aggregate function on coming answer. like i have 2 columns as ExamType and Obtained marks 1st will contains the values like ExamTypeCode ObtainedMarks 1 18 2 20 3 45 1 15 2 22 3 47 and i want the data like following without any aggregate function 1 2 3 ---------------- 18 20 45 15 22 47 I have tried the following query , its giving me ans but not like which i want SELECT * FROM ( SELECT StudentRegCode,ExamTypeCode,ObtainedMark FROM StudentSubjectMark ) AS SourceTable PIVOT ( MIN ([ObtainedMark]) FOR [ExamTypeCode] IN ([1],[3],[4])) AS PivotTable;

sudipta sanyal · Answer

Please Try This CREATE TABLE #StudentSubjectMark (ExamTypeCode INT, ObtainedMarks INT,StudentRegCode NVARCHAR(100)) GO --Populate Sample records INSERT INTO #StudentSubjectMark VALUES(1,18,'1A') INSERT INTO #StudentSubjectMark VALUES(2,20,'1A') INSERT INTO #StudentSubjectMark VALUES(3,22,'1A') INSERT INTO #StudentSubjectMark VALUES(2,20,'2A') INSERT INTO #StudentSubjectMark VALUES(3,45,'3A') INSERT INTO #StudentSubjectMark VALUES(1,15,'4A') INSERT INTO #StudentSubjectMark VALUES(2,22,'5A') INSERT INTO #StudentSubjectMark VALUES(3,27,'6A') DECLARE @DynamicPivotQuery AS NVARCHAR(MAX) DECLARE @ExamTypeCode AS NVARCHAR(MAX) --Get distinct values of the PIVOT Column SET @ExamTypeCode = STUFF((SELECT distinct ',' + QUOTENAME(ExamTypeCode) FROM #StudentSubjectMark ORDER BY 1 FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') SET @DynamicPivotQuery = 'SELECT StudentRegCode, ' + @ExamTypeCode + ' from ( select ExamTypeCode, ObtainedMarks,StudentRegCode from #StudentSubjectMark ) x pivot ( max(ObtainedMarks) for ExamTypeCode in (' + @ExamTypeCode + ') ) p ' EXEC sp_executesql @DynamicPivotQuery DROP TABLE #StudentSubjectMark Is it solve your problems

Nitin Sontakke · Answer

You can LOOK at your data and make sense at it, but how is computer is going to understand exactly ON WHAT BASIS, did you created those two rows from those 6 rows? There is absolutely nothing. Will you agree? In that case you will need 1 more column to distinguish.