returns 1 line from many rows NOT using cursor or

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

returns 1 line from many rows NOT using cursor or

Author

Topic

johnsql
Posting Yak Master

161 Posts

Posted - 2006-08-01 : 10:00:30

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 295 198 1 Cardin Henson, Dean Dusentoff, Kim Adams
1370 300 198 2 Dean Dusentoff, June Cleaver
1370 482 277 1 Kim Adams

Thanks very much in advance for any ideas.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-01 : 10:07:36

You can do without loop, but that is awkward and not pretty.

And besides, looping is very fast when implemented the right way.

Peter Larsson
Helsingborg, Sweden

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-01 : 10:08:15

Please post what u do so far, with regard to this.

hints :

Combine 2 tables , combine the 3rd one, and use
http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx

Srinika

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-01 : 10:24:59

[code]-- 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[/code]
Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-01 : 11:04:00

Note that if concatenated strings exceeds the length of 8000, you may need to use more than one variable

Madhivanan

Failing to plan is Planning to fail

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-08-01 : 12:58:50

The user-defined function method described in the link provided is by far the shortest, fastest and easiest way to do this. No loops needed, no cursors.

- Jeff

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-01 : 13:43:21

The function does exactly the same thing as my "SELECT @Teachers..." statement.

Comparing a SELECT DISTINCT ... , dbo.fnConcat and the while loop above, is the same, exept for one thing, I do not have the overhead of calling a function.

Peter Larsson
Helsingborg, Sweden

johnsql
Posting Yak Master

161 Posts

Posted - 2006-08-01 : 13:43:38

Madhivanan
You said "Note that if concatenated strings exceeds the length of 8000, you may need to use more than one variable". So, how can I do that?
Thanks.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-08-01 : 13:47:24

quote:
Originally posted by Peso

The function does exactly the same thing as my "SELECT @Teachers..." statement.

Comparing a SELECT DISTINCT ... , dbo.fnConcat and the while loop above, is the same, exept for one thing, I do not have the overhead of calling a function.

Peter Larsson
Helsingborg, Sweden

You have the overhead of table variables, while loops, and UPDATE statements.

Generally, the shortest technique (if not less efficient) is the way to go. the UDF is very concise and very clear.

- Jeff

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-01 : 14:45:18

quote:
Originally posted by johnsql

Madhivanan
You said "Note that if concatenated strings exceeds the length of 8000, you may need to use more than one variable". So, how can I do that?
Thanks.

If a teacher's name is about 20 characters long, you can have up to about 300 teachers per company, section, class and sectiontype.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-01 : 14:48:07

quote:
Originally posted by jsmith8858

You have the overhead of table variables, while loops, and UPDATE statements.

Generally, the shortest technique (if not less efficient) is the way to go. the UDF is very concise and very clear.

1. The function itself is a varchar that needs to be created for every row before distinct. Then distinct is applied over all columns, included the "functiongenerated" column. I create the table variable once.

2. Yes, I have a while loop, one for each distinct row. But that's also what the DISTINCT statement does, after going through all records (including the teachers column) in order to decide what is distinct.

3. The only different is the update statement, you are absolutely right about that.

I also agree with you that a function is a more elegant solution.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources