PIVOT AND UNPIVOT IN SQL SERVER

SELECT {non-pivoted column},

    [first pivoted column] AS {column name},

    [second pivoted column] AS {column name},

    ...

    [last pivoted column] AS {column name}

FROM

    ({SELECT query that produces the data})

    AS {alias for the source query}

PIVOT

(

    {aggregation function}({column being aggregated})

FOR

[{column that contains the values that will become column headers}]

    IN ( [first pivoted column], [second pivoted column],

    ... [last pivoted column])

) AS {alias for the pivot table}

{optional ORDER BY clause};

CREATE TABLE RESULT (STD NVARCHAR(200), SUBJ NVARCHAR(10), MARKS INT)

Insert into RESULT (STD,SUBJ,MARKS)

Select 'ARKA','SUB1',91

Union All

Select 'ARKA','SUB2',92

Union All

Select 'ARKA','SUB3',93

Union All

Select 'ARKA','SUB4',94

Union All

Select 'DEBADRITA','SUB1',81

Union All

Select 'DEBADRITA','SUB2',82

Union All

Select 'DEBADRITA','SUB3',83

Union All

Select 'DEBADRITA','SUB5',85

Union All

Select 'TIYA','SUB1',71

Union All

Select 'TIYA','SUB2',72

Union All

Select 'TIYA','SUB4',74

Union All

Select 'TIYA','SUB5',75

SELECT * FROM RESULT

PIVOT (MAX(MARKS) FOR SUBJ IN

([SUB1],[SUB2],[SUB3],[SUB4],[SUB5])) AS PVT

CREATE TABLE RESULT1 (STD NVARCHAR(100), SUB1 INT, SUB2 INT, SUB3 INT, SUB4 INT, SUB5 INT)

INSERT INTO RESULT1 (STD, SUB1, SUB2, SUB3, SUB4, SUB5)

SELECT 'ARKA',91,92,93,94,95

UNION ALL

SELECT 'DEBADRITA',81,82,83,84,85

UNION ALL

SELECT 'TIYA',71,72,73,74,75

SELECT * FROM RESULT1

SELECT STD,SUBJ,MARKS

FROM RESULT1

UNPIVOT(MARKS FOR SUBJ IN (SUB1, SUB2, SUB3, SUB4, SUB5) ) AS UNPVT