Off the top of my head, you could use a Cursor here to loop through the records and do the concatenation into a Temp table which you would then select off. Throw it in a sproc and you should be fine. If I get a quick 15 mins this morning I can have a look.
-----Original Message----- From: Osullivan Karl (RKB) Senior Analyst/Programmer [mailto:[EMAIL PROTECTED] Sent: 18 February 2004 09:29 To: CF - Development Group Subject: [ cf-dev ] OT: SQL7 query Hi All I'm trying to create a view such that a person who has more than 1 job has all their jobs shown as a concatenated string Eg. Person table id: 960 name: Karl id: 961 name: Paul id: 962 name: Sharon Jobs table id: 1 name: Fisherman id: 2 name: Spy id: 3 name: Analyst Programmer id: 4 name: Cleaner Persons_Jobs table person_id: 960 jobs_id: 2 person_id: 960 jobs_id: 4 person_id: 960 jobs_id: 3 person_id: 961 jobs_id: 1 Would give me.... Name Jobs Karl Spy|Cleaner|Analyst Programmer Paul Fisherman Sharon <null> -- it's important to include the names even if they don't have a job I've only got access to SQL7 and so therefore no UDFs Any ideas how I could do this without using CF? Karl Karl O'Sullivan Senior Analyst/Programmer Computer & Network Services UHCW NHS Trust Clifford Bridge Road COVENTRY CV2 2DX Contact: Tel (internal): 28952 Tel (external): +44 (0) 24 7696 8952 Email: [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> Web: www.uhcw.nhs.uk <http://www.uhcw.nhs.uk> This e-mail is from Reed Exhibitions (Oriel House, 26 The Quadrant, Richmond, Surrey, TW9 1DL, United Kingdom), a division of Reed Business, Registered in England, Number 678540. It contains information which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s) please note that any form of distribution, copying or use of this communication or the information in it is strictly prohibited and may be unlawful. If you have received this communication in error please return it to the sender or call our switchboard on +44 (0) 20 89107910. The opinions expressed within this communication are not necessarily those expressed by Reed Exhibitions. Visit our website at http://www.reedexpo.com -- ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] For human help, e-mail: [EMAIL PROTECTED]
