How to make Column to Row without an aggregate funtion in sql server 2005/8?

Question

For example, I need to change from

to

.

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 

Answer 1

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

Answer 2

Please check this post:

http://sqlservercodebook.blogspot.com/2008/04/row-to-column.html