如何在 sql server 2005/8 中使 Column to Row 没有聚合函数?

本文介绍了如何在 sql server 2005/8 中使 Column to Row 没有聚合函数?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

比如我需要从

到

.

我知道 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