on 2024 Jul 08 5:20 AM
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')
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;
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
User | Count |
---|---|
62 | |
10 | |
10 | |
6 | |
6 | |
6 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.