代码之家 › 专栏 › 技术社区 › Chris Cudmore

SQL Server PIVOT帮助

pivot sql-server-2005

1

Chris Cudmore · 技术社区 · 16 年前

Create Table ExamAnswers
{
   StudentID varchar(12),
   QuestionID int,
   Answer char(1)
}

这将被填满

Bob 1 a
Bob 2 c
...
Bob 100 b
Chris 1 c
Chris 2 d
...
Chris 100 null

Chris没有完成考试,但第100道问题存储为null,因此可以保证每个学生都有100行,但实际答案为null或字符。

现在我有一个报告要求,出于审计目的,我需要制作一个如下表:

ID    1 2 ... 100 
Bob   a c ... b
Chris c d ....null

所以我花了半天时间阅读PIVOT函数,但我就是不明白。

我认为这是PIVOT函数最简单的用法,我在任何地方都找不到一个像样的例子。帮助

2 回复 | 直到 16 年前

1

3

Irina C 16 年前

看看这篇文章: Using PIVOT and UNPIVOT

报价:

The following is annotated syntax for PIVOT.

SELECT <non-pivoted column> ,

    [first pivoted column] AS <column name> ,

    [second pivoted column] AS <column name> ,

    ...

    [last pivoted column] AS <column name>

FROM 

    ( <SELECT query that produces the data> ) 

    AS <alias for the source query>

PIVOT 

( 

    <aggregation function>( <column being aggregated> )

FOR 

[<column that contains the values that will become column headers>] 

    IN ( [first pivoted column] , [second pivoted column] ,

    ... [last pivoted column] )

) AS <alias for the pivot table>

<optional ORDER BY clause>

你的桌子可以是这样的:

Create Table ExamAnswers
(
   StudentID varchar(12) NOT NULL,
   QuestionID int NOT NULL,
   Answer int
)

SELECT StudentID, [1] as Q1,  [2] as Q2, [3] as Q3, [4] as Q4, [5] as Q5 
FROM 
(
SELECT StudentID, QuestionID, Answer
FROM dbo.ExamAnswers
) AS piv
PIVOT
(
AVG(Answer)
 FOR QuestionID IN ([1], [2], [3], [4], [5])
) AS chld

2

1

Chris Cudmore 16 年前

OK解决了。MAX或MIN将在char字段上工作。

Create Table ExamAnswers
{
   StudentID varchar(12),
   QuestionID int,
   Answer char(1)
}

如最初创建的

然后

SELECT StudentID, [1] as Q1,  [2] as Q2, [3] as Q3, [4] as Q4, [5] as Q5 
FROM 
(
SELECT StudentID, QuestionID, Answer
FROM dbo.ExamAnswers
) AS piv
PIVOT
(
MAX(Answer)
 FOR QuestionID IN ([1], [2], [3], [4], [5])
) AS chld

混乱在于选择了一个没有逻辑理由聚合任何东西的聚合。我应该提到的是,StudentID和QuestionID形成了一个复合键,因此对于任何给定的SID和QID对,只有一个可能的答案值。