Me parece que ya se ha publicado esto antes. Pero no pude encontrarlo... Como sea, con SQL Server 2005 y 2008 la opción más fácil son las funciones XML. Con SQL Server 2000, usa una tabla auxiliar de números (busca "numbers table" o "tally table" en google).
Te dejo un ejemplo fácil para SQL Server 2005 y 2008.
Código:
DECLARE @Xml AS XML,
@Text AS VARCHAR(200);
SET @Text = '5,10,15,20,30';
SET @Xml = '<x>' + REPLACE(@Text, ',', '</x><x>') + '</x>';
SELECT x.n.value('.', 'INT') AS n
FROM @Xml.nodes('/x') AS x(n);