Please be aware that the SAP SQL Anywhere Forum will be shut down on August 29th, 2024 when all it's content will be migrated to the SAP Community.

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

asked 08 Jul, 04:20

lina77's gravatar image

lina77
102
accept rate: 0%

edited 08 Jul, 11:31

Reg%20Domaratzki's gravatar image

Reg Domaratzki
7.9k343119

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

(08 Jul, 11:42) Chris Keating

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

(08 Jul, 21:16) lina77
Replies hidden

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...

(09 Jul, 01:28) Volker Barth

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;
permanent link

answered 09 Jul, 03:15

ArcoW's gravatar image

ArcoW
3013515
accept rate: 4%

edited 09 Jul, 03:20

i get this error :

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

(09 Jul, 22:24) lina77

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.

(2 days ago) ArcoW

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

(2 days ago) ArcoW

This should do the trick

select distinct
   S.MatricNo
  ,S.Name
  ,M.Semester
  ,(select Marks from #Marks where MatricNo = S.MatricNo and Semester = M.Semester and TestID = (select TestID from (select TestID,row_number() over (order by TestID) as RowNum from #Test) as DT where RowNum = 1 order by RowNum)) as Assignment_1
  ,(select Marks from #Marks where MatricNo = S.MatricNo and Semester = M.Semester and TestID = (select TestID from (select TestID,row_number() over (order by TestID) as RowNum from #Test) as DT where RowNum = 2 order by RowNum)) as Assignment_2
  ,(select Marks from #Marks where MatricNo = S.MatricNo and Semester = M.Semester and TestID = (select TestID from (select TestID,row_number() over (order by TestID) as RowNum from #Test) as DT where RowNum = 3 order by RowNum)) as Presentation
  ,(select Marks from #Marks where MatricNo = S.MatricNo and Semester = M.Semester and TestID = (select TestID from (select TestID,row_number() over (order by TestID) as RowNum from #Test) as DT where RowNum = 4 order by RowNum)) as Project
from #Students S
join #Marks M on M.MatricNo = S.MatricNo
join #Test T on T.TestID = M.TestId

permanent link

answered 09 Jul, 02:37

Frank%20Vestjens's gravatar image

Frank Vestjens
1.3k374866
accept rate: 20%

The answer does not meet my question. & query do have parameter. My final result is as i requred in my first post. The column result is dynamic depends on the semester parameter in table #Marks.

(09 Jul, 03:03) lina77
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×15

question asked: 08 Jul, 04:20

question was seen: 103 times

last updated: 2 days ago