returns 1 line from many rows NOT using cursor or loop
-
Knights,
I have 3 tables like.
table "Section"
--------------
companyID sectionID ClassID SectionTyID
----------- ----------- ----------- -----------
1370 295 198 1
1370 300 198 2
1370 482 277 1
table "SectionTeacher"
---------------------
companyID sectionID userID
----------- ----------- -----------
1370 295 189022
1370 295 201583
1370 295 241860
1370 300 201583
1370 300 242231
1370 482 241860
table "users"
-------------
companyID userID firstName lastName
----------- ----------- -------------------------------------------------- --------------------------------------------------
1370 189022 Cardin Henson
1370 201583 Dean Dusentoff
1370 241860 Kim Adams
1370 242231 June Cleaver
Now I want to combine the 3 tables in a query such that the result gives me combined teachers name for each sectionID in 1 line. But
I do not want to use loop or cursor because they increase the running time. Do you have any ideas to do so? The results I want
looks like (One sectionID may have more than 1 teacher being assigned to it).
companyID SectionID ClassID SectionTyID Teachers
----------- ---------- ------- ----------- ---------------------------------------------
1370 2951981 Cardin Henson, Dean Dusentoff, Kim Adams
1370 3001982 Dean Dusentoff, June Cleaver
1370 4822771 Kim Adams
Thanks very much in advance for any ideas.
-
-- prepare test data
declare @section table (companyid int, sectionid int, classid int, sectiontyid int)
insert @section
select 1370, 295, 198, 1 union all
select 1370, 300, 198, 2 union all
select 1370, 482, 277, 1
declare @sectionteacher table (companyid int, sectionid int, userid int)
insert @sectionteacher
select 1370, 295, 189022 union all
select 1370, 295, 201583 union all
select 1370, 295, 241860 union all
select 1370, 300, 201583 union all
select 1370, 300, 242231 union all
select 1370, 482, 241860
declare @users table (companyid int, userid int, firstName varchar(100), lastName varchar(100))
insert @users
select 1370, 189022, 'Cardin', 'Henson' union all
select 1370, 201583, 'Dean', 'Dusentoff' union all
select 1370, 241860, 'Kim', 'Adams' union all
select 1370, 242231, 'June', 'Cleaver'
-- Stage the data
DECLARE @Stage TABLE
(
CompanyID INT,
SectionID INT,
ClassID INT,
SectionTypeID INT,
Teacher VARCHAR(200)
 
INSERT @Stage
SELECT s.companyid,
s.sectionid,
s.classid,
s.sectiontyid,
u.firstName + ' ' + u.lastName
FROM @section s
INNER JOIN @sectionteacher st ON st.companyid = s.companyid
AND st.sectionid = s.sectionid
INNER JOIN @users u ON u.companyid = st.companyid
AND u.userid = st.userid
-- Do the actual work
DECLARE @Output TABLE
(
RowID INT IDENTITY(0, 1) Primary KEY CLUSTERED,
CompanyID INT,
SectionID INT,
ClassID INT,
SectionTypeID INT,
Teachers VARCHAR(8000)
 
INSERT @Output
(
CompanyID,
SectionID,
ClassID,
SectionTypeID
)
SELECT DISTINCT CompanyID,
SectionID,
ClassID,
SectionTypeID
FROM @Stage
ORDER BY CompanyID,
SectionID,
ClassID,
SectionTypeID
DECLARE @RowID INT,
@Teachers VARCHAR(8000),
@CompanyID INT,
@SectionID INT,
@ClassID INT,
@SectionTypeID INT
SELECT @RowID = MAX(RowID),
@Teachers = ''
FROM @Output
WHILE @RowID >= 0
BEGIN
SELECT @CompanyID = CompanyID,
@SectionID = SectionID,
@ClassID = ClassID,
@SectionTypeID = SectionTypeID
FROM @Output
WHERE RowID = @RowID
SELECT @Teachers = LEFT(@Teachers + CASE WHEN @Teachers = '' THEN '' ELSE ', ' END + z.Teacher, 8000)
FROM (
SELECT TOP 100 PERCENT Teacher
FROM @Stage
WHERE CompanyID = @CompanyID
AND SectionID = @SectionID
AND ClassID = @ClassID
AND SectionTypeID = @SectionTypeID
ORDER BY Teacher
) z
UPDATE @Output
SET Teachers = @Teachers
WHERE RowID = @RowID
SELECT @RowID = @RowID - 1,
@Teachers = ''
END
-- Show the output
SELECT CompanyID,
SectionID,
ClassID,
SectionTypeID,
Teachers
FROM @Output
ORDER BY RowID
N 56°04'39.16"
E 12°55'05.25" -
Watch out for this dropping your REAL tables, for SQL 2000:
SET NOCOUNT ON
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID('section') AND sysstat & 0xf = 3)
DROP TABLE section
GO
CREATE TABLE section (companyid int, sectionid int, classid int, sectiontyid int)
insert section
select 1370, 295, 198, 1 union all
select 1370, 300, 198, 2 union all
select 1370, 482, 277, 1
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID('sectionteacher') AND sysstat & 0xf = 3)
DROP TABLE sectionteacher
GO
CREATE TABLE sectionteacher (companyid int, sectionid int, userid int)
insert sectionteacher
select 1370, 295, 189022 union all
select 1370, 295, 201583 union all
select 1370, 295, 241860 union all
select 1370, 300, 201583 union all
select 1370, 300, 242231 union all
select 1370, 482, 241860
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID('users') AND sysstat & 0xf = 3)
DROP TABLE users
GO
CREATE TABLE users (companyid int, userid int, firstName varchar(100), lastName varchar(100))
insert users
select 1370, 189022, 'Cardin', 'Henson' union all
select 1370, 201583, 'Dean', 'Dusentoff' union all
select 1370, 241860, 'Kim', 'Adams' union all
select 1370, 242231, 'June', 'Cleaver'
PRINT 'fn_GetTeachers'
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.fn_GetTeachers') AND xtype IN (N'FN', N'IF', N'TF'))
DROP FUNCTION dbo.fn_GetTeachers
GO
CREATE FUNCTION dbo.fn_GetTeachers
(@CompanyID int, @SectionID int)
RETURNS varchar(4000)
AS
BEGIN
DECLARE @Teachers varchar(4000)
SELECT @Teachers = ISNULL(@Teachers+', ','')+ u.firstName+' '+u.lastName
FROM sectionteacher AS t
INNER JOIN users AS u ON t.companyid = u.companyid
AND t.userid = u.userid
WHERE t.companyid = @CompanyID
AND t.sectionid = @SectionID
RETURN @Teachers
END
GO
SET NOCOUNT OFF
SELECT companyid, sectionid, classid, sectiontyid
, dbo.fn_GetTeachers(companyid, sectionid) AS Teachers
FROM section
Andy
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply