Pivot with 3 tables

[Hazeleena]

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')

[chris_keating]

Can you share what the result set you are trying to achieve?

[Hazeleena]

The parameter to relate them is @Semester. Finally, it can be either as below based on @Semester

1st look

Matric No. | Name | Assignment 1 | Assignment 2 | Presentation | Project | Final | Grade | Point

888999 | Hazel | 15 | 10 | 10 | 20 | 45 | F | 0

OR 2nd Look

Matric No. | Name | Assignment 1 | Assignment 2 | Final | Grade | Point

888999 | Hazel | 15 | 10 | 45 | F | 0

[VolkerBarth]

I still don't get your requirements. What do you mean by PIVOT? Within SQL, a PIVOT operation is basically turning columns into rows. Do you instead mean to JOIN tables to relate those three tables? And what do you mean by parameter here? A query does not usually have a parameter, stored procedures do have...

If you show the desired full outcome (the complete expected result set), we might be able to help to show a query that would return that...

[awitter]

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]

i get this error :

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

[awitter]

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]

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