How to Flatten exploded data?

• glenn ward

  SSC Rookie

  Points: 31

  More actions

  December 17, 2004 at 5:21 pm

  #87600

  Hi - I have a database i'd like to clean up; the originator did a poor job mapping the XML data fields / values when he imported it to produce the table.  Instead of creating field names based on what was in the XML, he 'exploded' the data so that the field names change with each record. Here's what i mean:

  The current data looks like:

  UniqueID Value-Name Value-Value

  9B0C9257 Make Honda

  9B0C9257 Model Accord

  9B0C9257 Year 2004

  5D662793 Make Lexus

  5D662793 Model RX300

  5D662793 Year 2003

  5D662793 Make Ford

  5D662793 Model Mustang

  5D662793 Year 2002

  <continues for many thousands of records. Also, note how the same UniqueID can be repeated - indicating (analogy here) that an owner owns more than 2 cars>

  I want to 'flatten' the table above into this...

  UniqueID Make Model Year

  9B0C9257 Honda Accord 2004

  5D662793 Lexus RX300 2003

  5D662793 Ford Mustang 2002

  Any ideas?

  Thanks!

  Glenn / gwardatblargdotnet

• Farrell Keough

  SSCoach

  Points: 17414

  More actions

  December 17, 2004 at 6:43 pm

  #534394

  It looks like every three records comprise the one record you want.  If that is not true, you will probably need to create some vicious flow control, (i.e., IF... BEGIN/END   ELSE... BEGIN/END). 

  If it is every three records, you may try buffering each record into a #Temp table.  It looks like you can seperate each part by spaces [ CHAR(32) ].  I would suggest using CHARINDEX.  It has been my experience that you want to add another character to the end of your string, (maybe a pipe | ) to delineate where to stop and you want to subtract 1 from the CHARINDEX position to get the strings of interest, (i.e., "9B0C9257" "Make" "Honda"). 

  I probably have some old code that does something similar if you need an example. 

  This is a booger.  Good luck. 

  I wasn't born stupid - I had to study.