Numbering Rows where a person has more than one alias

• Debbie Edwards

  SSCrazy Eights

  Points: 9217

  More actions

  December 19, 2008 at 8:52 am

  #77164

  I originally posted in the 2005 forum so apologies...... Still unable to work this out and my other idea to only grab the latest alias based on last date doesnt work as the date isnt available.

  I have the following script

  DROP TABLE dbo.SOC_CARE_DATA_QUALITY_EXTRACT_ALIAS_DUPS_ONLY

  CREATE TABLE dbo.SOC_CARE_DATA_QUALITY_EXTRACT_ALIAS_DUPS_ONLY

  ([fi pin] INT, Alias varchar(100))

  INSERT INTO dbo.SOC_CARE_DATA_QUALITY_EXTRACT_ALIAS_DUPS_ONLY

  ([fi pin], Alias)

  SELECT 118007, 'Sara Jane smith'UNION ALL

  SELECT 118007, 'Sarah Smith'UNION ALL

  SELECT 118007, 'Sara Jayne Smith'UNION ALL

  SELECT 118007, 'Sara Smith'UNION ALL

  SELECT 118008, 'John Good'UNION ALL

  SELECT 118008, 'Jon Goode'UNION ALL

  SELECT 118008, 'Jonny Good'UNION ALL

  SELECT 118008, 'Jonny Goode'UNION ALL

  SELECT 118009, 'Debora Ellis'UNION ALL

  SELECT 118009, 'Debbie Ellis'UNION ALL

  SELECT 118010, 'Jon Junior'

  I am wanting to run script that brings back the following (Numbering the names)

  118007, 'Sara Jane smith',1

  118007, 'Sarah Smith' ,2

  118007, 'Sara Jayne Smith' ,3

  118007, 'Sara Smith' ,4

  118008, 'John Good',1

  118008, 'Jon Goode' ,2

  118008, 'Jonny Good' ,3

  118008, 'Jonny Goode',4

  118009, 'Debora Ellis',1

  118009, 'Debbie Ellis',2

  118010, 'Jon Junior',1

  I eventually get 1 row of data instead of 4

  fi Id Alias

  118007, 'Sara Jane smith/Sarah Smith/Sara Jayne Smith/Sara Smith'

  Any help would be much apprieciated. Im thinking you start with creating a clustered index but Im not sure....

  CREATE CLUSTERED INDEX [Alias_Numbering] ON [dbo].[SOC_CARE_DATA_QUALITY_EXTRACT_ALIAS_DUPS_ONLY]([Fi Pin], [Alias ]) ON [PRIMARY]

  Debbie

• Garadin

  One Orange Chip

  Points: 29613

  More actions

  December 19, 2008 at 1:45 pm

  #915628

  Honestly, even though you posted in the wrong forum, you're still better off not re-posting. The other thread has ideas already thrown out and discarded, and more information about your environment.

  Other thread: http://www.sqlservercentral.com/Forums/Topic622738-338-1.aspx

  That said, on to your issue. Running totals would be extremely easy to implement here... IF you have any kind of sequence field in the parent table. Anything like an Identity field, or createdate field would work great. Do you have something like this?

  Actually... let me make sure I understand your overall issue. Are you trying to do a data cleanup where you use the most recent name... or a concatenation where you just show all the different names. Concatenating data of this nature seems silly to me, you should just make it correct and go forward with that.

  Seth Phelabaum

  ---

  Consistency is only a virtue if you're not a screwup. 😉

  Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]