How to: select normalized view from denormalized data using CTE?

Question

This is a very standard newbie question, but I am looking for an expert clever way to do this with little code. (I know how to do this long hand, with procedural code and cursors, so we can skip that part of the answer.) Basically, I have a de-normalized data set for which I need to extract a normalized table in a set processing way. The raw data comes from Excel, which I attach as a table to query from MS SQL. A single field (in Excel and therefore SQL) has a comma separated list of departments that each product participates.

I know that I should normalize the data, but it is coming from Excel (and it is not practical for the user to do so). I know I can do this with a procedure to loop through each record (like a cursor or with statement) but that seems unnecessary with the tools available today. Is there a single statement where I can extract a primary key with a "participates" field for these records?

Think of the source as:

ProductIDDepartments
 123      1,3,5,15  
 456      2,4,5,16  

I did the normal select * from dbo.split( CommaSepField) but this only works on one record at a time (and you cannot provide a field's value in with a table valued function. Not to mention, how do you union all these single result sets?

Unionizing all the result sets got me thinking about Common Table Expressions, but I cannot quite figure out how to (1) get the primary key in with the expanded fields, and (2) how to format the query to work.

The output of course would be:

ProductIDDept
 123      1  
 123      3  
 123      5  
 123      15 
 456      2  
 456      4  
 456      5  
 456      16 

I researched this quite a bit in StackOverflow and Google, but didn't really find an answer that could apply. I apologize if I missed it. Please send links :D.

Answer 1

You can actually use your comma separation TVF, by joining using the CROSS APPLY operator:

select * from Table CROSS APPLY  dbo.split(CommaSepField)