如何用其他列选择值..？ [英] how to select the value with other column..?

问题描述

学生姓名学生成绩

raj 50

raj 45

raj 60

raj 70
raj 65

megh 87

megh 63

megh 89

megh 35
megh 25

naveen 60

naveen 70

naveen 65

naveen 87
naveen 63



我想在选择以下格式时更改数据



studentname studentmark sub1 sub2 sub3 sub4 sub5

----------- ------------ ---- ---- ---- ---- ----

student name student mark
raj 50
raj 45
raj 60
raj 70
raj 65
megh 87
megh 63
megh 89
megh 35
megh 25
naveen 60
naveen 70
naveen 65
naveen 87
naveen 63

I want to change the data while selecting in below format

studentname studentmark sub1 sub2 sub3 sub4 sub5
----------- ------------ ---- ---- ---- ---- ----

推荐答案

使用piviot。 Follwoing是一些参考链接。



http://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server [ ^ ]

在SQL查询中使用Pivot的简单方法 [ ^ ]

http： //technet.microsoft.com/en-in/library/ms177410(v=sql.105).aspx [ ^ ]

Use piviot. Follwoing are some the link for reference.

http://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server[^]
Simple Way To Use Pivot In SQL Query[^]
http://technet.microsoft.com/en-in/library/ms177410(v=sql.105).aspx[^]

请参阅此链接，

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 / [ ^ ]



编辑：

试试这样，

我只是创建一个类似于你的表格。

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/[^]


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')

并尝试像这样的Pivot，

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;

我在这里显示5个主题数据。



编辑2：在这里你可以使用order by for学生姓名

I'm showing 5 Subject Data's here.

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

编辑3：如果不起作用，则无需使用像这样的CTE.try，我在查询结束时使用order by。

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.

这篇关于如何用其他列选择值..？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！