I have set of data that looks like this:
PRODUCT_ID PRODUCT_DESC TYPE_ID
2 Meat 1
2 Pasta 2
2 Milk 3
2 Bread 4
2 Fruit 11
3 Meat 1
3 Pasta 2
3 Milk 3
3 Bread 4
3 Herbs 5
3 Vegetables 6
3 Beer 7
3 Wine 8
3 Brandy 9
3 Juice 10
3 Fruit 11
3 Soda 12
4 Meat 1
4 Pasta 2
4 Milk 3
4 Bread 4
4 Fruit 11
5 Meat 1
5 Pasta 2
5 Milk 3
5 Bread 4
5 Herbs 5
5 Vegetables 6
5 Beer 7
5 Wine 8
5 Brandy 9
5 Juice 10
5 Fruit 11
5 Soda 12
6 Pasta 2
6 Herbs 5
6 Vegetables 6
6 Beer 7
6 Wine 8
6 Brandy 9
6 Juice 10
6 Soda 12
7 Pasta 2
7 Herbs 5
7 Vegetables 6
7 Beer 7
7 Wine 8
7 Brandy 9
7 Juice 10
7 Soda 12
The data above should be grouped by Product id. Here is example of how I would like to organize the data:
PRODUCT ID
2 TYPE_ID 1,2,3,4,11
PRODUCT_DESC Meat,Pasta,Milk,Bread,Fruit
3 TYPE_ID 1,2,3,4,5,6,7,8,9,10,11,12
PRODUCT_DESC Meat,Pasta,Milk,Bread,Herbs,Vegetables,Beer,Wine,Brandy,Juice,Fruit,Soda
4 TYPE_ID 1,2,3,4,11
PRODUCT_DESC Meat,Pasta,Milk,Bread,Fruit
5 TYPE_ID 1,2,3,4,5,6,7,8,9,10,11,12
PRODUCT_DESC Meat,Pasta,Milk,Bread,Herbs,Vegetables,Beer,Wine,Brandy,Juice,Fruit,Soda
6 TYPE_ID 2,5,6,7,8,9,10,12
PRODUCT_DESC Pasta,Herbs,Vegetables,Beer,Wine,Brandy,Juice,Soda
7 TYPE_ID 2,5,6,7,8,9,10,12
PRODUCT_DESC Pasta,Herbs,Vegetables,Beer,Wine,Brandy,Juice,Soda
The example above basically groups the data items by product id and then creates list for product description and type. I'm wondering what is the best way to achieve this? I know that one way would be to loop over the data create structure and populate the keys. Then int he second iteration check if key exists int he structure and append the elements to the list.
This would work but obviously I would like to eliminate looping twice over the same set of data if possible. Also, I'm wondering if would be possible to organize the data in SQL Sybase ASE?
Here is solution that I got in ColdFusion using structure and arrays:
strProducts = structNew();
for ( row in qryProducts ) {
if ( !structKeyExists(strProducts , row.product_id) ) {
strProducts[row.product_id] = {type_id: [row.type_id], product_desc: [row.product_desc]};
} else {
arrayAppend(strProducts[row.product_id].type_id, row.type_id);
arrayAppend(strProducts[row.product_id].product_desc, row.product_desc);
}
}
If anyone have suggestions on how this can be accomplished please let me know. Thank you.
