T-SQL: how can I separate comma separated values ​​from column to rows?

I have the following table (reference count 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 shortcut way to transform data like this using MS SQL Server 2005?

thank

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

+2


source to share


2 answers


What version of SQL Server are you using?

Previous 2005

Use the service number table as described here .
I have used it many times with great success.



2005 and after

Read developer Mitch Wheat's answer from developer.

+1


source


Please look:



+3


source







All Articles