Inserting row with custom format and values from other function

user_7347563 0
Viewed 58

I am creating a database for a fictional school. I have a table Courses (PK Coursecode nvarchar(50), Credits int, FK Teacher nvarchar(50), Name nvarchar(50)), another Teachers, and another named CourseTeachers, referencing the two and storing data about hours worked.

What I want to accomplish is to create a function that takes year as argument and returns a table in the format

FIRST NAME | LAST NAME | course1/period | course2/period | course3/period | course4/period
teacher1 fn | teacher1 ln | hrs worked course1 | ...

Note that the top row is data in the table, not attributes.

For this I created a function getFourCoursesByYear that simply does the following

SELECT TOP 4 CourseTeachers.Course, CourseTeachers.Teacher 
FROM CourseTeachers 
WHERE CourseTeachers.year = @year

The problems I have:

  • How do I insert a row in the specified format using the data from the function?
  • How do I calculate the hours worked and return in the proper format?

The solution I have considered so far involves subqueries and is something along the lines of

 DECLARE @table TABLE 
                (
                    Firstname nvarchar(50), 
                    Lastname nvarchar(50), 
                    c1 nvarchar(50), 
                    c2 nvarchar(50), 
                    c3 nvarchar(50), 
                    c4 nvarchar(50)
                );

INSERT INTO @table
VALUES('First Name', 'Last Name', 
       (SELECT Course FROM getFourCoursesByYear(@year)), ...)

However, as you can see the code is incomplete and doesn't work.

tsql sql-server sql
2 Answers

The correct syntax of your query is equal to the following code

DECLARE @table TABLE 
(
    Firstname nvarchar(50), 
    Lastname nvarchar(50), 
    c1 nvarchar(50), 
    c2 nvarchar(50), 
    c3 nvarchar(50), 
    c4 nvarchar(50)
);

Declare @FirstName nvarchar(100) = 'First Name'
Declare @LastName nvarchar(100) = 'Last Name'



INSERT INTO @table(FirstName,Lastname,c1) 
SELECT @FirstName,@LastName,Course 
FROM getFourCoursesByYear(@year)
user_150195360

You can pivot on a row-number. What you want your row-number to be ordered by is up to you.

DECLARE @year int;

SELECT
  CoursePeriod1 = MAX(CASE WHEN rn = 1 THEN CoursePeriod END),
  CoursePeriod2 = MAX(CASE WHEN rn = 2 THEN CoursePeriod END),
  CoursePeriod3 = MAX(CASE WHEN rn = 3 THEN CoursePeriod END),
  CoursePeriod4 = MAX(CASE WHEN rn = 4 THEN CoursePeriod END),
  Teacher1 = MAX(CASE WHEN rn = 1 THEN Teacher END),
  Teacher2 = MAX(CASE WHEN rn = 2 THEN Teacher END),
  Teacher3 = MAX(CASE WHEN rn = 3 THEN Teacher END),
  Teacher4 = MAX(CASE WHEN rn = 4 THEN Teacher END),
....
FROM (
    SELECT *,
      rn = ROW_NUMBER() OVER (ORDER BY courseId)
    FROM getFourCoursesByYear(@year)
)
user_148689970
Related