Use Of PIVOT in SQL Server without aggregate function

Sukhada Vader

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;

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

sudipta sanyal8y ago
Accepted 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

0
Nitin Sontakke8y ago
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.
0