T-SQL: How can I split comma seperated values from a column to rows?

Question

Hi everbody,

I have the following table (the number of the references is variable):

Id | FK_ID| Reference |
-----------------------
1    2100   GI2, GI32
2    2344   GI56

And I need the following result:

Id | FK_ID| Reference |
-----------------------
1    2100   GI2 
2    2100   GI32
3    2344   GI56

Is there any short way to transform the data like this using MS SQL Server 2005?

Thank you

EDIT: Direct link to the solution: http://www.sommarskog.se/arrays-in-sql-2005.html#iter-list-of-strings

Answer 1

Please see:

• Arrays and Lists in SQL Server 2005

Answer 2

Which SQL Server version are you using?

Prior 2005

Use utility Numbers table as explained here.
I've used it many times before with great success.

2005 and after

Read fellow developer Mitch Wheat's answer.