New to SQL Server. Have a lot of MS Access experience and know how to do this task in Access vba but I have not been able to figure out how to replicate this task in a SQL server UDF.
I have a qry that appends new rows to an existing table. Among the columns there are: col1, col2, col3, col4, and col5. These all have the same data type (Long) AND same source of the value (think person ID). The qry can generate many combinations of these 5 columns but in some cases, it is possible that 25 combinations are actually the same 5 values - just in different columns.
To eliminate duplicate combinations, in vba I have a function that:
- puts the 5 col values into an array
- sorts the array
- then concatenates the ORDERED col values into a created field that will be appended to the Primary Key field on the target table.
So these values:
col1=333, col2=555, col3=111, col4=222, and col5=444 (all in the same row)
(note: another row could have the same values in different columns: col1=222, col2=333, col3=444, col4=555, and col5=111 but would in fact be a duplicate row in the target table - as demonstrated by the resulting value below)
would be sorted and concatenated into:
PrimaryKey field = 111222333444555
Any guidance would be greatly appreciated!!!