Geek asks and answers

Stored procedure that returns a table of 2 combined

I am trying to write a stored procedure that returns a result combining two table variables that look something like this.

Name | LastName | course | course | course  | course  <- Columns
Name | LastName | DVA123 | DVA222 | nothing | nothing <- Row1
Pete   Steven       200                               <- Row2    
Steve  Lastname              50                       <- Row3

Of these 3 tables

Staff table :

Name | LastName | SSN |
Steve  Lastname   234 
Pete   Steven     132

Table Course Instance :

Course | Year | Period |
DVA123   2013     1 
DVA222   2014     2

Table Present :

Course | SSN |  Year  | Period | Hours |
DVA123   234    2013      1       200 
DVA222   132    2014      2       50

I take @yearas a parameter that will determine which year in the course will be displayed as a result.

ALTER proc [dbo].[test4]
   @year int
as
begin

-- I then declare the 2 tables which I will then store the values from the tables
DECLARE @Table1 TABLE( 
   Firstname varchar(30) NOT NULL, 
   Lastname varchar(30) NOT NULL 
); 

DECLARE @Table2 TABLE( 
   Course varchar(30) NULL
);

Declare @variable varchar (max) - a variable to save the cursor value, and then set the cursor 1 to 4

I want the top 4 results / courses that I later ordered for the period of the year

declare myCursor1 CURSOR
for SELECT top 4 period from Course instance
where year = @year

open myCursor1

fetch next from myCursor1 into @variable
--print @variable

while @@fetch_status = 0
    Begin

    UPDATE @Table2
    SET InstanceCourse1 = @variable
    where current of myCursor1

    fetch next from myCursor1 into @variable
    print @variable

    End

Close myCursor1
deallocate myCursor1

insert into @table1
select 'Firstname', 'Lastname'

insert into @table1
select Firstname, Lastname from staff order by Lastname

END

select * from @Table1 -- for testing purposes
select * from @Table2 -- for testing purposes
--Then i want to combine these tables into the output at the top

, , , .

- , . , (0-4) , @table2.

+3
3

Ok. . sql, . , , . ROW_Number() . , , , . . , .

DECLARE @Year INT
SET @Year = 2014
DECLARE @Query NVARCHAR(2000)

DECLARE @CoursesColumns NVARCHAR(2000) 
SET @CoursesColumns = (SELECT '''' + Course + ''' as c' + CAST(ROW_NUMBER() OVER(ORDER BY Year,     Period) AS nvarchar(50)) + ',' AS 'data()'
FROM AttendedBy where [Year] = @Year
for xml path(''))
SET @CoursesColumns = LEFT(@CoursesColumns, LEN(@CoursesColumns) -1)
SET @CoursesColumns = 
CASE 
    WHEN CHARINDEX('c1', @CoursesColumns) = 0 THEN @CoursesColumns + 'NULL as c1, NULL as c2, NULL as c3, NULL as c4'
    WHEN CHARINDEX('c2', @CoursesColumns) = 0 THEN @CoursesColumns + ',NULL as c2, NULL as c3, NULL as c4'
    WHEN CHARINDEX('c3', @CoursesColumns) = 0 THEN @CoursesColumns + ', NULL as c3, NULL as c4'
    WHEN CHARINDEX('c4', @CoursesColumns) = 0 THEN @CoursesColumns + ', NULL as c4'
    ELSE @CoursesColumns
END

DECLARE @Courses NVARCHAR(2000) 
SET @Courses = (SELECT Course + ' as c' + CAST(ROW_NUMBER() OVER(ORDER BY Year, Period) AS     nvarchar(50)) + ',' AS 'data()'
FROM AttendedBy where [Year] = @Year
for xml path(''))
SET @Courses = LEFT(@Courses, LEN(@Courses) -1)
SET @Courses = 
CASE 
    WHEN CHARINDEX('c1', @Courses) = 0 THEN @Courses + 'NULL as c1, NULL as c2, NULL as c3, NULL as c4'
    WHEN CHARINDEX('c2', @Courses) = 0 THEN @Courses + ',NULL as c2, NULL as c3, NULL as c4'
    WHEN CHARINDEX('c3', @Courses) = 0 THEN @Courses + ', NULL as c3, NULL as c4'
    WHEN CHARINDEX('c4', @Courses) = 0 THEN @Courses + ', NULL as c4'
    ELSE @Courses
END

DECLARE @CoursePivot NVARCHAR(2000) 
SET @CoursePivot = (SELECT  Course + ',' AS 'data()'
FROM AttendedBy where [Year] = @Year
for xml path(''))
SET @CoursePivot = LEFT(@CoursePivot, LEN(@CoursePivot) -1)

SET @Query = 'SELECT Name, LastName, c1, c2, c3, c4
FROM (
SELECT ''Name'' as name, ''LastName'' as lastname, ' +  @CoursesColumns +
' UNION

SELECT Name, LastName,' + @Courses +
' FROM(

SELECT 
    s.Name
    ,s.LastName
    ,ci.Course
    ,ci.Year
    ,ci.Period
    ,CAST(ab.Hours AS NVARCHAR(100)) AS Hours
FROM Staff s
LEFT JOIN AttendedBy ab
ON
s.SSN = ab.SSN
LEFT JOIN CourseInstance ci
ON
ab.Course = ci.Course
WHERE ci.Year=' + CAST(@Year AS nvarchar(4)) +
' ) q
PIVOT(
    MAX(Hours)
FOR 
    Course
IN (' + @CoursePivot + ')
)q2
)q3'


SELECT @Query
execute(@Query)

: , . . enter image description here

+2

DECLARE @CourseNameString varchar(max),
    @query  AS NVARCHAR(MAX);

    SET @CourseNameString=''
select @CourseNameString = STUFF((SELECT distinct ',' + QUOTENAME(Course) 
                   FROM Attended where [Year]= 2013
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = '
select Name,LastName,'+@CourseNameString+' from Staff as e inner join (

 SELECT * FROM
(SELECT [Hours],a.SSN,a.Course as c FROM Attended as a inner JOIN Staff as s
ON s.SSN = s.SSN) p
PIVOT(max([Hours])FOR c IN ('+@CourseNameString+')) pvt)p
 ON e.SSN = p.SSN'

  execute(@query)
+2

Use a subquery like this:

SELECT Firstname, Lastname, (select instanceCourse1 from table2) as InstanceCourse1 from Table1
0
source

More articles:


All Articles