grouping data from lists

[jonathan driller]

I am so sure I did this before but am struggling with it now...any help
appreciated: I have a list of papers that I need to sort under topical
categories, some papers go under multiple categories....
item1 'category1' title 1 item2 'category1','category2' title 2 item3
'category3',''category4' title 3 item4 ''category4' title 4 Output should
be: 'category1' title 1 title 2 'category2' title 2 title 3 'category3'
title 3 'category4' title 4 I input this to the database from a form and so
these category items are a list. I can't do a loop to insert the category items
to a separate table because I need to associate them with the primary key of
the other data that is associated with them in the main content table. If I
input them all together as a list then when I do a group the list mess gets
output... <cfoutput query='getWPs' group='category'> #category# <cfoutput> #'
getWPs.title# </cfoutput> </cfoutput> I am not much on T-SQL but see you can
parse the list and input the items into another table but then I am back to
figuring how to get the primary key from the rest of the data that goes into
the main table.... Any help greatly appreciated...

[Rob]

A little more info would be helpful.

Your output doesn't make sense to me. It looks like 'Category 2' should only have title 2
and 'Category 4' should have title 3 and title 4.

Can you display your lists? It might make what you are looking for more clear.

[philh]

As mentioned in many other threads, storing lists in a single field and
expecting them to represent separate and discrete data elements is bad. You
need a DB redesign that includes a many-to-many table which holds the
relationships among the categories and the titles. Sounds like you need more
work than just this relationship, but it's necessary. T-SQL doesn't handle
this 'list in a field' stuff because it's not normalized data. Case closed.
Now that my soapbox has collapsed under the sheer weight of my tirade, here's
an approach that should get you a normalized relationship. 1. Create a table,
tblTitlesCategories, with a primary key, and fields to hold the title foreign
key and the category foreign key. (You do have primary keys on the Categories
and Titles tables, right?) 2. Assuming you have tables for the titles and
categories, issue a SELECT statement: select a.*, b.titlePrimaryKey from items
a, tblTitles b where b.TitleName = a.Title Spin through this recordset using
the following pseudocode: For each record: For each category field in the
record: (CFLOOP on the 'list' from that field) select primarykey from
Categories where CategoryName = [the current value from CFLOOP Insert into the
many-to-many table the category key (which you just grabbed) and the title key
from the main recordset Next item in the Category list Next item in the
recordset Should do ya. HTH,