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

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

ED开发者_StackOverflow社区IT: Direct link to the solution: http://www.sommarskog.se/arrays-in-sql-2005.html#iter-list-of-strings

---

Please see:

• Arrays and Lists in SQL Server 2005

---

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.