Here's some info that might help (though I'm not sure about 2000):
http://msdn2.microsoft.com/en-us/library/ms182741
- Jeff
Patrick Branley wrote:
does anyone know how this can be done with MSSQL ?
at the moment on 2000 we write individual DB functions to achieve it.
but it means we need one for each column we want to turn to a list :(
Pat
On 11/9/05, *Barney Boisvert* <[EMAIL PROTECTED]
<mailto:[EMAIL PROTECTED]>> wrote:
If right now you've got this:
SELECT t1.cola, t2.codoelb
FROM t1
INNER JOIN t2 ON t1.id <http://t1.id> = t2.id <http://t2.id>
ORDER BY t1.cola, t2.colb
you should change it to this:
SELECT t1.cola, GROUP_CONCAT(t2.colb ORDER BY t2.colb) AS colbList
FROM t1
INNER JOIN t2 ON t1.id <http://t1.id> = t2.id <http://t2.id>
GROUP BY t1.cola
ORDER BY t1.cola
With this specific example you could also do it with a subquery:
SELECT cola, (
SELECT GROUP_CONCAT(colb ORDER BY colb)
FROM t2
WHERE id = t1.id <http://t1.id>
) AS colbList
FROM t1
ORDER BY cola
I didn't actually run any of those statements, but they should be
close.
cheers,
barneyb
On 11/8/05, Baz <[EMAIL PROTECTED]
<mailto:[EMAIL PROTECTED]>> wrote:
>
> Thanks for the tips guys - it seems everyone's leaning towards a
DB solution
> so I'm going to look into Barney's suggestion of using
GROUP_CONCAT (I'm
> using MySQL)
>
> Cheers,
> Baz
>
--
Barney Boisvert
[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>
360.319.6145
http://www.barneyb.com/
Got Gmail? I have 100 invites.
----------------------------------------------------------
You are subscribed to cfcdev. To unsubscribe, send an email to
[email protected] <mailto:[email protected]> with the words
'unsubscribe cfcdev' as the subject of the email.
CFCDev is run by CFCZone (www.cfczone.org
<http://www.cfczone.org>) and supported by CFXHosting
(www.cfxhosting.com <http://www.cfxhosting.com>).
An archive of the CFCDev list is available at
www.mail-archive.com/[email protected]
<http://www.mail-archive.com/[email protected]>
----------------------------------------------------------
You are subscribed to cfcdev. To unsubscribe, send an email to
[email protected] with the words 'unsubscribe cfcdev' as the subject
of the email.
CFCDev is run by CFCZone (www.cfczone.org) and supported by CFXHosting
(www.cfxhosting.com).
An archive of the CFCDev list is available at
www.mail-archive.com/[email protected]
----------------------------------------------------------
You are subscribed to cfcdev. To unsubscribe, send an email to
[email protected] with the words 'unsubscribe cfcdev' as the subject of the
email.
CFCDev is run by CFCZone (www.cfczone.org) and supported by CFXHosting
(www.cfxhosting.com).
An archive of the CFCDev list is available at
www.mail-archive.com/[email protected]
