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
