sql - 如何在sql server中水平旋转表

Question

我的表格的列如下:

Sr.no  Subject  No of class attended    
-------------------------------------
1       English           3
2       Maths             4
3       SocialScience     5

我想要这种格式的表格

English    Maths   SocialScience
---------------------------------
3            4          5

我尝试过这个:

Select case when subject ='Maths' then COUNT(No_of_Candidates) else null  end as Maths

但这样我得到的数据是这样的:

 English    Maths   SocialScience
---------------------------------
   3            
               4
                         5

请帮我解决这个问题..

Answer 1

正如您所说，您不希望这样的输出:

 English    Maths   SocialScience
---------------------------------
   3            
               4
                         5

您需要像这样使用子查询:

SELECT English,Maths,SocialScience
FROM (
SELECT Subject,No_of_class_attended
  FROM mytable) up
PIVOT 
(Sum([No_of_class_attended]) 
 for Subject in ([English],[Maths],[SocialScience])) p

输出:

English    Maths   SocialScience
---------------------------------
3            4          5

  

See this SQLFiddle

更多信息请参见SQL SERVER – PIVOT and UNPIVOT Table Examples