filter duplicate students via T-SQL

• kevin_nikolai

  SSCarpal Tunnel

  Points: 4343

  More actions

  April 19, 2013 at 6:52 am

  #394632

  /*

  I need to filter for duplicate students:

  ---------------------------------------

  For same student, if one or both instance/s of IDNo is null then do the following:

  1.

  if both instances of Tel3 are identical for same Student (Alison), if they are

  use it to populate field Sort with the value.

  2.

  if both instances of Tel3 are not identical for same Student (Carmen), check if both instances of Tel2 are identical for same Student, if they are use it to populate field Sort with the value.

  3.

  if both instances of Tel3 and Tel2 are not identical for same Student (Amber), check if an instance of Tel3 & Tel2 are identical for same Student, if they are use it to populate field Sort with the value.

  */

  SELECT 'Alison' as Student, NULL as IDNo, '1111' as Tel3, '1111' as Tel1, NULL as Sort UNION ALL

  SELECT 'Alison' as Student, '80405088' as IDNo, '1111' as Tel3, '1111' as Tel1, NULL as Sort UNION ALL

  SELECT 'Amber' as Student, NULL as IDNo, '5555' as Tel3, '6666' as Tel1, NULL as Sort UNION ALL

  SELECT 'Amber' as Student, NULL as IDNo, '7777' as Tel3, '7777' as Tel1, NULL as Sort UNION ALL

  SELECT 'Carmen' as Student, NULL as IDNo, '8888' as Tel3, '8888' as Tel1, NULL as Sort UNION ALL

  SELECT 'Carmen' as Student, '20083085' as IDNo, '9999' as Tel3, '8888' as Tel1, NULL as Sort

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  April 19, 2013 at 7:20 am

  #1608200

  duplicate post. Direct replies here. http://www.sqlservercentral.com/Forums/Topic1444358-392-1.aspx

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/