Home Forums SQL Server 2012 SQL Server 2012 - T-SQL Querying a Supersession Two Column Table with Multiple Supersessions in both Columns RE: Querying a Supersession Two Column Table with Multiple Supersessions in both Columns

  • February 3, 2014 at 4:22 pm

    #1685683

    Hi,

    I would like the result returned as

    'SFP500160','KIT BRAKE LINING',58.02,{array of previous and current part numbers}

    ie

    'SFP500160','KIT BRAKE LINING',58.02,{ 'RTC5756','STC8572','STC3765','STC9150','STC9191','SFP500160' }

    or two split results, whichever is easier

    'SFP500160','KIT BRAKE LINING',58.02

    'RTC5756'

    'STC8572'

    'STC3765'

    'STC9150'

    'STC9191'

    'SFP500160'

    Baring in mind,that we are starting the search with only one of these part numbers known to the end user.

    The part supersession trail length can be quite long, I think the longest so far is 177. They average 5-10 in most instances though.

    The way the supersessions table is provided to me is a reason for my headache. Usually, you would expect the search to filter "old part number > new part number > old part number > new part number" and so forth but in this instance, the later part number can be in either the "OriginalPartNumber" column or the "NewPartNumber" column. With more than 70,000 rows and a monthly update of the Supersessions table in this format, it would be nice to have a clean and easy way to find the part number supersession trail.

    Thanks.