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