我希望输出如下在sql server 2005中的顺序隔离 [英] I want the output as follows in course wise segregation in sql server 2005
本文介绍了我希望输出如下在sql server 2005中的顺序隔离的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
选择 日期,会话,faculty_code,课程来自 Tb_SCh_TIme_Table WHERE COURSE = ' PH2' 订单 按 date ,Session,course;
输出如下;
日期会话faculty_code课程
14/1/2013 1 NR PH2
14/1/2013 4 NR PH2
但我想在sql server 2005中输出如下;
PH2(课程)
14/1/2013 1 2 3 4(会话)
NR NR
我该怎么办?对于上面的输出如何在sql server 2005中编写查询。
解决方案
你可以使用PIVOT条款。
http://msdn.microsoft.com/en-us/library/ms177410(v = sql.105).aspx [ ^ ]
例如:
SELECT *
FROM (
SELECT *
FROM [Tb_SCh_TIme_Table]
)P
PIVOT(
MAX([faculty_code])
FOR [session] IN ([ 1 ],[ 2 ],[ 3 ],[ 4 ])
) AS PVT
WHERE course = ' PH2'
以上代码将为您提供如下输出
< pre lang =text> date course 1 2 3 4
---------- ------ ----- ----- ----- - ---
2013-01-14 PH2 NR NULL CM NR
select Date,Session,faculty_code,course from Tb_SCh_TIme_Table WHERE COURSE= 'PH2' order by date,Session,course;
output as follows;
Date session faculty_code course
14/1/2013 1 NR PH2
14/1/2013 4 NR PH2
But i want the output as follows in sql server 2005;
PH2 (course)
14/1/2013 1 2 3 4 (Session)
NR NR
how can i do? for the above output how to write the query in sql server 2005.
解决方案
you can use PIVOT clause for this.
http://msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx[^]
for ex:
SELECT * FROM ( SELECT * FROM [Tb_SCh_TIme_Table ] ) P PIVOT ( MAX([faculty_code]) FOR [session] IN ([1],[2],[3] ,[4]) ) AS PVT WHERE course='PH2'
above code will give you the output as below
date course 1 2 3 4 ---------- ------ ----- ----- ----- ----- 2013-01-14 PH2 NR NULL CM NR
这篇关于我希望输出如下在sql server 2005中的顺序隔离的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文