querry: rows must be displayed as columns

hi all,

currently i have a table:
studentId course score year
6.1 math 9 2007
6.1 french 5 2007
6.1 science 8 2007
6.1 math 6 2006
6.1 french 5 2006
6.1 science 5 2006
6.2 math 4 2007
6.2 french 6 2007

is it possible to have a query that gives this as result:
student year mathscore frenchscore sciencescore
6.1 2007 9 5 8
6.1 2006 6 5 5
6.2 2007 4 6 NULL

use PIVOT operator (if you SQL Server 2005).

Code Snippet

Create Table #score (

[studentId] Varchar(100) ,

[course] Varchar(100) ,

[score] int ,

[year] int

);

Insert Into #score Values('6.1','math','9','2007');

Insert Into #score Values('6.1','french','5','2007');

Insert Into #score Values('6.1','science','8','2007');

Insert Into #score Values('6.1','math','6','2006');

Insert Into #score Values('6.1','french','5','2006');

Insert Into #score Values('6.1','science','5','2006');

Insert Into #score Values('6.2','math','4','2007');

Insert Into #score Values('6.2','french','6','2007');

Select

[studentId]

, [year]

, [math]

, [french]

, [science]

From

#score S

Pivot(

Max([score]) For

[course] in ([math], [french], [science])

) as Pvt

|||

You could ue PIVOT function:

Code Snippet

create table #Scores

(

studentid int,

course varchar(10),

score int,

year int

)

insert into #Scores values(6.1,'math', 9, 2007)

insert into #Scores values(6.1,'french', 5, 2007)

insert into #Scores values(6.1,'science', 8, 2007)

insert into #Scores values(6.1,'math', 6, 2006)

insert into #Scores values(6.1,'french', 5, 2006)

insert into #Scores values(6.1,'science', 5, 2006)

insert into #Scores values(6.2,'math', 4, 2007)

insert into #Scores values(6.2,'french', 6, 2007)

select studentid, year, [math],[french], [science]

FROM #Scores PIVOT( MAX(SCORE) FOR course IN ([math],[french], [science]) ) as pvt

|||hi try this..

DECLARE @.Subjects TABLE (
studentId numeric(10,1)
, course varchar(20)
, score numeric
, [year] int
)

INSERT
INTO @.Subjects
SELECT 6.1, 'math', 9, 2007 UNION ALL
SELECT 6.1, 'french', 5, 2007 UNION ALL
SELECT 6.1, 'science', 8, 2007 UNION ALL
SELECT 6.1, 'math', 6, 2006 UNION ALL
SELECT 6.1, 'french', 5, 2006 UNION ALL
SELECT 6.1, 'science', 5, 2006 UNION ALL
SELECT 6.2, 'math', 4, 2007 UNION ALL
SELECT 6.2, 'french', 6, 2007

SELECT DISTINCT
s.studentId
, s.[year]
, m.score as mathscore
, f.score as frenchscore
, c.score as sciencescore
FROM @.Subjects s LEFT OUTER JOIN
(
SELECT *
FROM @.Subjects
WHERE course = 'math'
) m ON s.studentId = m.studentId
AND s.[year] = m.[year] LEFT OUTER JOIN
(
SELECT *
FROM @.Subjects
WHERE course = 'french'
) f ON s.studentId = f.studentId
AND s.[year] = f.[year] LEFT OUTER JOIN
(
SELECT *
FROM @.Subjects
WHERE course = 'science'
) c ON s.studentId = c.studentId
AND s.[year] = c.[year]|||

If you use SQL Server 2000 (This query will work with 2005 also)..

Code Snippet

Create Table #score (

[studentId] Varchar(100) ,

[course] Varchar(100) ,

[score] int ,

[year] int

);

Insert Into #score Values('6.1','math','9','2007');

Insert Into #score Values('6.1','french','5','2007');

Insert Into #score Values('6.1','science','8','2007');

Insert Into #score Values('6.1','math','6','2006');

Insert Into #score Values('6.1','french','5','2006');

Insert Into #score Values('6.1','science','5','2006');

Insert Into #score Values('6.2','math','4','2007');

Insert Into #score Values('6.2','french','6','2007');

Select

studentId

, year

, Sum(Case When Course = 'math' Then score End) as [math]

, Sum(Case When Course = 'french' Then score End) as [french]

, Sum(Case When Course = 'science' Then score End) as [science]

From

#score [main]

Group By

studentId,year

|||nice post mani,|||hi,
in SQL Server 2000 you can try this query :

Code Snippet

select studentID, year
, (select score from #tbl where course = 'math' and studentID = x.studentid and year = x.year) as matchscore
, (select score from #tbl where course = 'science' and studentID = x.studentid and year = x.year) as sciencescore
, (select score from #tbl where course = 'french' and studentID = x.studentid and year = x.year) as frenchscore
from #tbl x

- clintz