如何在 sql server 2005/8 中使 Column to Row 没有聚合函数? [英] How to make Column to Row without an aggregate function in sql server 2005/8?

问题描述

比如我需要从

到

.

我知道 PIVOT 是用来做这个的，但它需要一个聚合函数；就我而言，我不需要聚合只需要列到行.

I know PIVOT is for that, but it requires an aggregate function; and for my case, I donot need to aggregate only need column to row.

您可以使用以下示例数据:

You can use the following sample data:

    CREATE TABLE[StudentScores] 
( 
[UserName] NVARCHAR(20),
[Subject] NVARCHAR(30),
[Score]FLOAT,
) 
GO

INSERT INTO[StudentScores]SELECT'Nick','Chinese',80 

INSERT INTO[StudentScores]SELECT'Nick','Maths',90 

INSERT INTO[StudentScores]SELECT'Nick','English',70 

INSERT INTO[StudentScores]SELECT'Nick','Biology',85 

INSERT INTO[StudentScores]SELECT'Kent','Chinese',80 

INSERT INTO[StudentScores]SELECT'Kent','Maths',90 

INSERT INTO[StudentScores]SELECT'Kent','English',70 

INSERT INTO[StudentScores]SELECT'Kent','Biology',85 

推荐答案

如果每个主题有一个记录，您可以使用 MIN 或 MAX.

If there is going to be one record per subject you can use MIN or MAX.

SELECT *
FROM [StudentScores]
PIVOT
(
  MIN(Score)
  FOR [Subject] IN ([Chinese],[Maths],[English],[Biology])
)
AS p

这篇关于如何在 sql server 2005/8 中使 Column to Row 没有聚合函数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！