Skip to main content
B
bigbrain28
Inspiring
September 29, 2009
Answered

Combine dupe records to one, where <> data becomes new field(s)

  • September 29, 2009
  • 1 reply
  • 1134 views

My brain seems to be having a tough time with this concept, although it really isn't that complicated. What I have is a 'membership' list (provided to us) and for some odd reason instead of having 3,4, or 5 columns exist for the different 'categories' each member may occupy, the table has ONE field for 'category' and in each case where a member has more than one 'category' desination there is a duplicate record, with only the 'category' being different.

On top of this anomoly, certain members have many locations - so names may repeat, but address or phone numbers might be different. I'll need to treat those as individual listings.

Normally we have one record for each member, and category fields in multiple within that record - like any sane person would do. What I can't seem to figure out is the correct sql or cf coding to combine the multiple (assumed same) members into one record while taking the unique/different categories (from the dupes) and simply adding them to the output of the base member data.

Example AS IS;

1, Bobs Lamps, lamp sales

2, Bobs Lamps, lamp repair

3, Bobs Lamps, Coconut Bikinis

Desired Output;

1, Bobs Lamps, lamp sales, lamp repair, Coconut Bikinis

If I can output a CSV list in the desired format I can re-import it generating the new columns. However not all listings will have multiple category occurances, so I'll need null placeholders for the potential 4 category columns.

I'm really hoping this is simpler than I can see at the moment, and one of you superstars can help a guy out!

Thanks in advance!

This topic has been closed for replies.
Correct answer ilssac

First of all.  I think you do not necesarilly have an Anomoly!  This would depend on whether this is all the data, or just the way the data was extracted.

What you have there is a normalized one to many relationship where a foreign key is duplicated with unique data for the category value for each record.

I really hope you are NOT planning to turn that into this horriable table design.

NameCategor1Category2Category3
Bobs Lampslamp saleslamp repairCoconut Bikinis

But as to how to display that without the repeating names... This would depend somewhat on what type of variable the data is in.  If it is in a record set from a query it is trivial with nested <cfoutput...> loops.

<cfoutput query="myData" group="name">
#myData.name#
<cfoutput>
#myData.category#
</cfoutput>
</cfoutput>

If it is not in a recordset variable then the looping would be more complex, but the idea is basicaly the same.

1 reply

ilssac
ilssacCorrect answer
Inspiring
September 29, 2009

First of all.  I think you do not necesarilly have an Anomoly!  This would depend on whether this is all the data, or just the way the data was extracted.

What you have there is a normalized one to many relationship where a foreign key is duplicated with unique data for the category value for each record.

I really hope you are NOT planning to turn that into this horriable table design.

NameCategor1Category2Category3
Bobs Lampslamp saleslamp repairCoconut Bikinis

But as to how to display that without the repeating names... This would depend somewhat on what type of variable the data is in.  If it is in a record set from a query it is trivial with nested <cfoutput...> loops.

<cfoutput query="myData" group="name">
#myData.name#
<cfoutput>
#myData.category#
</cfoutput>
</cfoutput>

If it is not in a recordset variable then the looping would be more complex, but the idea is basicaly the same.

B
bigbrain28Author
Inspiring
September 29, 2009

Ian,

Wow. See I knew I was just missing that simple GROUP usage! I used it as you described and it did the trick. How have I gone so long without understanding how the group command works? Weird indeed.

As for the table layout having 4 columns for cat's rather than a FK to a seperate table; The cats are finite, never more than 5 and the kind of processing we do with the data just makes it easier if its all in a single record. We do directory publishing for Chambers of Commerce, and I have written a CF app that query's these supplied DB's and creates an XML doc for import & autotagging in InDesign.

Thanks for your keen eye and quick response.

ilssac
ilssac
Inspiring
September 29, 2009

But the idea is not to build a limit into the table.

The idea is to use a normalized, best pratice of a one to many relationship like the data you are receiving.

Then to use simple reporting output to extract this data in any requried format now and into any possible future.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices