Refer this links,
http://www.mindfiresolutions.com/Using-Pivot-Operator-in-SQL-Server-1181.php[
^]
http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/[
^]
http://sqlhints.com/2014/03/10/pivot-and-unpivot-in-sql-server/[
^]
Edit:
Try like this,
I just create a table similar to your table.
CREATE TABLE #test (student_name nvarchar(max) , student_marks float , subject nvarchar(max))
INSERT INTO #test VALUES('Raj',50,'Sub1')
INSERT INTO #test VALUES('Raj',55,'Sub2')
INSERT INTO #test VALUES('Raj',60,'Sub3')
INSERT INTO #test VALUES('Raj',65,'Sub4')
INSERT INTO #test VALUES('Raj',70,'Sub5')
INSERT INTO #test VALUES('Rajesh',70,'Sub1')
INSERT INTO #test VALUES('Rajesh',75,'Sub2')
INSERT INTO #test VALUES('Rajesh',80,'Sub3')
INSERT INTO #test VALUES('Rajesh',85,'Sub4')
INSERT INTO #test VALUES('Rajesh',90,'Sub5')
And Try Pivot like this,
select student_name, [1], [2], [3],[4],[5]
from
(
select student_name, student_marks,
row_number() over(partition by student_name order by subject) rn
from #test
) d
pivot
(
max(student_marks)
for rn in ([1], [2], [3],[4],[5])
) piv;
I'm showing 5 Subject Data's here.
Edit2: Here you can use order by for student name
with cte as (select student_name,[1], [2],[3],[4],[5]
from
(
select student_name, student_marks,
row_number() over(partition by student_name order by subject) rn
from #test
) d
pivot
(
max(student_marks)
for rn in ([1], [2], [3],[4],[5])
) piv) select * from cte order by student_name desc
Edit3:If it not work,No need to use CTE.try like this,I used "order by" here at end of query.
select student_name,[1], [2],[3],[4],[5]
from
(
select student_name, student_marks,
row_number() over(partition by student_name order by subject) rn
from #test
) d
pivot
(
max(student_marks)
for rn in ([1], [2], [3],[4],[5])
) piv order by student_name desc ;
Hope this may helps you.
Submit an article or tip