如何在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

请帮我解决这个问题。

- user1274646

---

在看到同样的问题后，我找到了这个答案，忍不住开了个玩笑，因为我最初的措辞是“如何翻转桌子”，直到我意识到这个双关语。 - Swift - Friday Pie

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

这段文字的大意是：

点击这个 SQLFiddle 链接

更多信息请参见SQL SERVER - PIVOT 和 UNPIVOT 表示例

- Himanshu

Answer 2

使用PIVOT功能

SELECT *
FROM yourtable
PIVOT 
(Sum([No of class attended]) for Subject in ([English],[Maths],[SocialScience])) p

- podiluska

---

OP已经拥有这种类型的输出。他/她不需要这个输出。 - Himanshu

是的，但是您在此答案中提供的代码将无法满足OP的要求。 - Himanshu

但是我们应该编写清晰（或精确）的答案（代码），以满足OP的要求。请在此fiddle中查看您代码的结果。OP在问题中已经说明他不需要那个结果。 - Himanshu

我认为你对@hims056有些过于苛刻了，而且没有意识到你自己回答中的一个缺陷。你想让我指出错误吗？还是你更愿意自己找出来？ - Andriy M

Answer 3

Podiluska的解决方案是正确的，如果你想在表格中添加其他主题并且不想改变查询，我只想分享一种动态解决方案。然而，它在长度方面有一些限制，但你肯定可以在某些情况下使用它：

DECLARE @SQL nvarchar(MAX)
DECLARE @ColNames nvarchar(max)

SET @ColNames = ''

SELECT @ColNames = (CASE WHEN subjects.Subject IS NOT NULL THEN @ColNames + '[' + subjects.Subject + '],' ELSE '' END)
FROM subjects

SET @ColNames = LEFT(@ColNames, LEN(@ColNames) - 1)


EXEC('SELECT *
FROM subjects
PIVOT 
(Sum([classNum]) for Subject in (' + @ColNames + ')) p')

这里是一个SQL Fiddle。

- András Ottó

---

@podiluska的答案并不完全正确，你的也不是。你有没有实际查看查询的输出？它会产生多行结果，而OP想要它们在一行中。 - Andriy M

Answer 4

如果您不想使用PIVOT关键字，可以简单地使用MAX：

Select MAX(case when subject ='Maths' then No_of_Candidates else null  end) as Maths
,MAX(case when subject ='English' then No_of_Candidates else null  end) as English
,MAX(case when subject ='SocialScience' then No_of_Candidates else null  end) as SocialScience
from tableName 

如果表格中包含多个学生的结果，您需要使用GROUP BY，例如：

Select MAX(case when subject ='Maths' then No_of_Candidates else null  end) as Maths
,MAX(case when subject ='English' then No_of_Candidates else null  end) as English
,MAX(case when subject ='SocialScience' then No_of_Candidates else null  end) as SocialScience
from tableName Group By StudentID

任何值都大于 NULL，因此 MAX 函数将消除 NULL 值。

- Sam Anwar

---

如果在包含聚合或子查询的表达式上执行聚合函数，将会出现“无法在表达式上执行聚合函数”的错误。如果使用max函数，则会出现此错误。我该如何解决这个问题？ - user1274646

哦，我明白了，从所有三个表达式中完全删除COUNT函数，然后让我知道你得到了什么。我已经修改了上面的代码。 - Sam Anwar

如果我删除 Count，我将无法计算 no_of_candidates 的数量，因为它实际上是从表中计算行数的。 - user1274646

好的，我想你的第一个问题有点混淆。所以你需要对表格中显示的数字（出勤次数）做任何操作吗？在你提供的示例中，你没有计算任何内容，而只是将第三列中现有的数字转换为行。但是你列出了COUNT(No_of_Candidates)函数！No_of_Candidates列从哪里来的？ - Sam Anwar

Answer 5

我遇到了一个需要将行转置为列的要求，我希望根据数据动态增加列（而不像英语、数学等硬编码它们，你懂的）

这是我的示例数据

代码：

DECLARE @SQL NVARCHAR(MAX)=''
DECLARE @ColNames NVARCHAR(max)=''

;WITH mycte
AS 
( 
    SELECT *
    ,ROW_NUMBER() OVER ( PARTITION BY SubjectLearnt ORDER BY SubjectLearnt) AS RN
    from STUDENTS
)
SELECT @ColNames = (CASE WHEN mycte.SubjectLearnt IS NOT NULL THEN @ColNames + mycte.SubjectLearnt + ',' ELSE '' END)
FROM mycte
WHERE RN=1
SET @ColNames = LEFT(@ColNames, LEN(@ColNames) - 1)

EXEC('SELECT *
FROM students
PIVOT 
( Sum([subjectMarks]) for SubjectLearnt in (' + @ColNames + ')) p')

- GDS