May 29, 2003 at 10:17 am
Given the following (highly simplified) tables, can I write a select statement that will return me one row per entry in the clients table, with the entries in the qualifications table summarised into a single field ? Or do I need to load the qualifications table into a cursor, and run through that to create a string ?
Client Table
ClientID int,
ClientName varchar
Qualifications Table
ClientID int,
Qualification varchar
The result set I'm looking for would be something like;
client name Qualifications
----------- --------------
john smith BSc, MSc
chris jones
joe bloggs BA
And the data in the tables...
client
1 John Smith
2 Chris Jones
3 Joe Bloggs
qualifications
1 BSc
3 BA
1 MSc
Thanks !
Jonathan
May 29, 2003 at 10:50 am
Here is a way to do it without a cursor, although maybe this is not much better.
-- create tables
CREATE TABLE qualifications(ClientID int, Qualifications varchar(20))
Create table Client (ClientID int,ClientName varchar(20))
-- populate the tablea
insert into qualifications values(1,'BSc')
insert into qualifications values(3,'BA')
insert into qualifications values(1,'MSc')
insert into client values(1,'John Smith')
insert into client values(2,'Chris Jones')
insert into client values(3,'Joe Bloggs')
-- declare variables
declare @p char(1000)
declare @top int
declare @m int
declare @sm-2 int
declare @name char(20)
-- Print Report Heading
print 'Client Name ' + 'Qualifications'
print '-------------------- ' + '------------------------------------------'
set @p = ''
select top 1 @top =clientid from client order by clientid desc
-- set @m to the first id number
select top 1 @m = clientid, @name=clientname from client order by clientid
-- Process until no more clients
while @m <= @top
begin
-- string together all items with a comma between
select @p = rtrim(@p) + ', '+ Qualifications
from qualifications a
where Clientid = @m
-- print detail row
print @name + ' ' + rtrim(substring(@p,3,len(@p)))
-- increment clientid number
select top 1 @m = clientid, @name=clientname from client where clientid > @m order by clientid
set @p = ''
if @m = @sm-2 set @m = @top + 1
end
-- REMOVE TABLEs
DROP TABLE qualifications, client
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
May 30, 2003 at 10:03 am
Many thanks for the suggestion. I'll play around with it, but I think I'll probably end up with a cursor !
Regards,
Jonathan
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply