GROUP BY problem

[mensudb]

Hello,

I have a table with two columns: Code and Collection.
The table looks like:

Code Collection
------ ----------
10A-35 ABIGAJL
10B-28 AVALON
15K-35 ANJALI
10A-35 WISTERIA
10A-35 CELESTE

How to create a cursor which also have two columns, for code and collection, so that data are grouped by code, and second column have all collections where given code is belongin to.
For example, my cursor should looks like:

Code Collection
------ --------------------------
10A-35 ABIGAJL, WISTERIA, CELESTE
10B-28 AVALON
15K-35 ANJALI

Thanks

Mensud

 

[Stella740pl]

First that comes to mind is to create a writable cursor, index original table on code and collection combination; and then add new records to the cursor while SCANning through the table. So if you encountered a new code, create a new record in the cursor, otherwise just check,


IF AT(ALLTRIM(tmpCollection), Collection)=0
REPLACE Collection WITH Collection+", "+ALLTRIM(tmpCollection)
ENDIF

 

[jimstarr]

Hi Mensud,

Same idea as Stella:

CREATE CURSOR myoutput (outcode c(10), outcoll c(50))
SELECT mytable
SET ORDER TO code && assumes existence of this index
GO TOP
previous = code
collect = ''
SCAN
IF CODE != previous
INSERT INTO myoutput VALUES(previous, collect)
previous = code
collect = ''
ENDIF
IF EMPTY(collect)
collect = collection
ELSE
collect = ', ' + collection
ENDIF
ENDSCAN

Jim