VALIDATING FROM AND TO COLUMNS AGAINST PARAMETER TABLE

• Nilssond

  Hall of Fame

  Points: 3285

  More actions

  February 8, 2010 at 3:55 pm

  #156654

  I have a table of data where there are 2 columns which hold

  from and to values of a code set which is listed in a parameter table.

  Data Example is:

  FROM (column) TO (column)

  A0001 A0009

  B0011 B00012

  Parameter Table Holds the following:

  Value

  A0001

  A0002

  A0003

  A0004

  A0005

  A0006

  A0007

  A0008

  A0009

  A0010

  A0011

  B0001

  B0002

  B0003

  B0004

  B0005

  B0006

  B0007

  B0008

  B0009

  B0010

  B0011

  B0012

  B0013

  I need to comma-separate each value in the range of FROM and TO

  in the data table into a string after validating that the values in the range actually exists in the parameter table.

  Any suggestions?

• wendell.malpas

  Grasshopper

  Points: 20

  More actions

  February 10, 2010 at 2:34 am

  #1116635

  Hi There Nilssond,

  Let me try if I get your problem right and kindly take a look at the solution I provided below.

  First of all, it would easy if you can provide an SQL script with sample data that would relate to your problem...something like this:

  DECLARE @data TABLE (

  RowIDbigint PRIMARY KEY IDENTITY NOT NULL,

  [FROM]nvarchar(20),

  [TO]nvarchar(20)

  )

  INSERT INTO @data

  SELECT 'A0001', 'A0009' UNION ALL

  SELECT 'B0011', 'B0012'

  DECLARE @Parameter TABLE (

  RowIDbigint PRIMARY KEY IDENTITY NOT NULL,

  [Value] nvarchar(20)

  )

  INSERT INTO @Parameter

  SELECT 'A0001' UNION ALL

  SELECT 'A0002' UNION ALL

  SELECT 'A0003' UNION ALL

  SELECT 'A0004' UNION ALL

  SELECT 'A0005' UNION ALL

  SELECT 'A0006' UNION ALL

  SELECT 'A0007' UNION ALL

  SELECT 'A0008' UNION ALL

  SELECT 'A0009' UNION ALL

  SELECT 'A0010' UNION ALL

  SELECT 'A0011' UNION ALL

  SELECT 'B0001' UNION ALL

  SELECT 'B0002' UNION ALL

  SELECT 'B0003' UNION ALL

  SELECT 'B0004' UNION ALL

  SELECT 'B0005' UNION ALL

  SELECT 'B0006' UNION ALL

  SELECT 'B0007' UNION ALL

  SELECT 'B0008' UNION ALL

  SELECT 'B0009' UNION ALL

  SELECT 'B0010' UNION ALL

  SELECT 'B0011' UNION ALL

  SELECT 'B0012' UNION ALL

  SELECT 'B0013'

  For the suggestion, you can actually use FOR XML PATH to comma-separate each value. Please check on the following code below and let know if this is what you are looking for.

  SELECT

  (SELECT STUFF(

  (SELECT

  ',' + Param.[Value] AS 'data()'

  FROM @Parameter Param

  WHERE Param.[Value] BETWEEN Data.[FROM] AND Data.[TO]

  FOR XML PATH('')

  ), 1, 1, ''

  )

  ) as Parameters

  FROM @data Data

  Thanks,

  Wendell