cancel
Showing results for 
Search instead for 
Did you mean: 

Pivot with 3 tables

Hazeleena
Explorer
0 Kudos
779

I have 3 table. Assuming that those table is from database table not temporary table. How do convert those table to PIVOT without using PIVOT functions. Thank you.

CREATE TABLE #Students (
MatricNo VARCHAR(20) PRIMARY KEY,
Name VARCHAR(100),
Final1 INT,
Grade varchar(2)
)

INSERT INTO #Students VALUES(888999, 'Hazel', 221, 'F')

CREATE TABLE #Marks(
MatricNo VARCHAR(20),
Semester INT,
TestID INT,
Marks INT
)

INSERT INTO #Marks VALUES(888999, 1, '1', 15)
INSERT INTO #Marks VALUES(888999, 1, '2', 10)
INSERT INTO #Marks VALUES(888999, 1, '3', 10)
INSERT INTO #Marks VALUES(888999, 1, '4', 20)
INSERT INTO #Marks VALUES(888999, 2, '1', 25)
INSERT INTO #Marks VALUES(888999, 2, '2', 20)
INSERT INTO #Marks VALUES(888999, 2, '3', 30)

CREATE TABLE #Test(
TestID INT,
Test Name VARCHAR(40)
)

INSERT INTO #TestVALUES(1, 'Assignment_1')
INSERT INTO #TestVALUES(2, 'Assignment_2')
INSERT INTO #TestVALUES(3, 'Presentation')
INSERT INTO #TestVALUES(4, 'Project')
View Entire Topic
awitter
Participant

Best I come to your answer (which is, btw, not really structured set) is this:

begin
  declare @testnames nchar(40) array = (select array_agg(TestName) from #test);
  declare @SemesterToShow int = 1;

  select * from 
  (
   select StudentMatricNo, StudentName, marksMarks, TestName, StudentFinal1, StudentGrade
   from 
     #Students 
     left outer join #Marks on MarksMatricNo = StudentMatricNo and MarksSemester = @SemesterToShow
     left outer join #Test on TestID = MarksTestID
  ) Students
  PIVOT
  ( 
  avg(MarksMarks) Grade 
  for TestName in @testnames
  ) as PivotedTable
end;
Hazeleena
Explorer
0 Kudos

i get this error :

SQL Error [102] [42000]: Incorrect syntax near 'array'.

awitter
Participant
0 Kudos

That is strange, I used this in isqlc, as I do with every query. Everything between begin..end is watcom sql, as I normally use (missing all kinds of important triggers in T-sql).

Besides, I changed the field names, for it is so annoying to have same field names in different tables. Just forgot to add those create statements.

awitter
Participant
0 Kudos

Output: StudentMatricNo,StudentName,StudentFinal1,StudentGrade,Assignment_1_Grade,Assignment_2_Grade,Presentation_Grade,Project_Grade '888999','Hazel',221,'F',15.0,10.0,10.0,20.0