This is a very standard question for newbies, but I'm looking for an expert smart way to do this with a little code. (I know how to make this long arm, with procedural code and cursors, so we can skip this part of the answer.) Basically, I have an unnormalized data set for which I need to extract a normalized table into a given processing method . The raw data comes from Excel, which I attach as a table to a query from MS SQL. One field (in Excel and therefore SQL) has a comma-separated list of departments in which each product participates.
I know that I have to normalize the data, but it comes from Excel (and this is not practical for the user). I know that I can do this using a procedure to cycle through each record (for example, a cursor or with an instruction), but this seems unnecessary with the tools available today. Is there one statement where I can extract the primary key with the "participates" field for these entries?
Think of the source as:
ProductID Departments
123 1,3,5,15
456 2,4,5,16
I made the usual * selection from dbo.split (CommaSepField), but it only works one record at a time (and you cannot specify the value of a field using the function associated with the table. Not to mention how you combine all these single result sets?
The unification of all result sets made me think about the general expressions of the table, but I can’t understand how (1) to get the primary key with extended fields and (2) how to format the query for work.
The conclusion, of course, will be:
ProductID Dept
123 1
123 3
123 5
123 15
456 2
456 4
456 5
456 16
I studied this quite a bit on StackOverflow and Google, but actually did not find an answer that could be applied. I apologize if I missed this. Please send links: D.