CODEWITHSUNDEEP
sqlsql-servert-sqlpivot
airbai
airbai

Reputation: 3966

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

For example, I need to change from

alt text

to

alt text .

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

Upvotes: 1

Views: 2195

Answers (3)

dinesh028
dinesh028

Reputation: 2187

The new PIVOT operator in 11g can help you achieve desired output. Check this for example http://querydb.blogspot.in/2014/05/get-data-in-rows-and-aggregates-into.html

Upvotes: 0

Taryn
Taryn

Reputation: 247720

I cannot quite tell from your original question which field you want to transform - subject or score. However, you can use a PIVOT to perform this. If you know the number of columns you want to change from rows to columns, then you can use a static pivot (similar to the other answer). If you do not know the number of columns to transform, then you can use a dynamic pivot:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(subject) 
                    from test
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


set @query 
      = 'SELECT username,' + @cols + ' from 
         (
            select username, subject, score
            from test
         ) x
         pivot 
         (
            avg(score)
            for subject in(' + @cols + ')
         ) p '

execute(@query)

See SQL Fiddle with Demo

I used the AVG() aggregate in the event a user has more than one score per subject.

Upvotes: 1

anivas
anivas

Reputation: 6547

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

Upvotes: 1

Related Questions