将列转换为行 [英] convert columns into rows

问题描述

 date sess code course 
 14-Jan-13 1 CM AFF 
 14-Jan-13 3 CM AFF 
 17-Jan-13 1 NR CTF 

i想在sql server中使用查询输出如下，我该怎么办请回复。这对我很有帮助。< br $>


<前lang =text> AFF（课程）
1 2 3 4（sess）
14-Jan-13 CM CM

 CTF（课程）
 1 2 3 4（sess）
 17-Jan-13 NR 
 

我想要上面的输出，使用查询。



请回复。这对我很有帮助。

解决方案

检查以下查询

  SELECT  * 
  FROM （
  SELECT  * 
  FROM  [YOUR_TABLE_NAME] 
）P 
 PIVOT（
 MAX（[code]） 
  FOR  [sess]  IN （[ 1 ]，[ 2 ]，[ 3 ]，[ 4 ]）
） AS  PVT 

它返回如下输出

 date course 1 2 3 4 
 ------ ---- ------ ----- ----- ----- ----- 
 2013-01-14 AFF CM NULL CM NULL 
 2013- 01-17 CTF NR NULL NULL NULL 

您可以通过添加其中 课程列的子句




通过以下链接...研究它们并尝试使用数据库表...

http://stackoverflow.com/ question / 11323024 / how-to-convert-columns-to-rows-in-sql-server [ ^ ]

http://msdn.microsoft.com/ en-us / library / ms177410（v = sql.105）.aspx [ ^ ]

http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/ [ ^ ]

....它被称为PIVOT和UNPIVOT表




date	        sess	code	course
14-Jan-13	1	CM	AFF
14-Jan-13	3	CM	AFF
17-Jan-13	1	NR	CTF



i want the output as follows in sql server using query,how can i do please reply.it is very helpful for me.



               AFF (course)
        1   2  3  4 (sess)
14-Jan-13   CM     CM





              CTF  (course)
        1   2  3  4 (sess)
17-Jan-13   NR



I want the above output, using query.

please reply.it is very helpful for me.

解决方案

check the following query

SELECT *
FROM (
SELECT *
FROM [YOUR_TABLE_NAME]
) P
PIVOT (
MAX([code])
FOR [sess] IN ([1],[2],[3] ,[4])
) AS PVT

It returns the output as follows

date       course 1     2     3     4
---------- ------ ----- ----- ----- -----
2013-01-14 AFF    CM    NULL  CM    NULL
2013-01-17 CTF    NR    NULL  NULL  NULL

you can modify that by adding the where clause for course column




go through following links...study them and try for ur database table's...
http://stackoverflow.com/questions/11323024/how-to-convert-columns-to-rows-in-sql-server[^]
http://msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx[^]
http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/[^]
....it is called as PIVOT and UNPIVOT Table




这篇关于将列转换为行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！