inline sql with an array

[timc]

I have data which looks like below

actid labname
100 CKS
200 CKS;HDP;LAS

I need the data to be

actid labname
200 CKS
200 HDP
200 LAS

The ; is the seperator

For a reporting product I created a sp which created a temp table and then using my function below built. problem is the product won't allow me to create a temp table. With what I have below anyone have any creative ideas I could use. In-line sql, subquery views?

select enc_id,labcnt,order_name,date_due


reate FUNCTION fn_GET_ARRAY_VALUE(
@DELIMITER VARCHAR(100),
@STRING VARCHAR(1000),
@ARRAY_POSITION INT)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @CURRENT_POSITION INT
DECLARE @VALUE VARCHAR(8000)
SET @CURRENT_POSITION = 0

WHILE @CURRENT_POSITION<@ARRAY_POSITION
AND CHARINDEX(@DELIMITER,@STRING,0)>0
BEGIN
SET @STRING =
SUBSTRING(@STRING,
CHARINDEX(@DELIMITER, @STRING, 0)
+LEN(@DELIMITER),
LEN(@STRING)
-CHARINDEX(@DELIMITER, @STRING, 0)
+ LEN(@DELIMITER)
)
SET @CURRENT_POSITION = @CURRENT_POSITION + 1
END

IF CHARINDEX(@DELIMITER,@STRING,0)=0
SET @VALUE = @STRING
ELSE
SET @VALUE = SUBSTRING(@STRING, 0,
CHARINDEX(@DELIMITER, @STRING, 0)
)

RETURN(LTRIM(RTRIM(@VALUE)))
END

[skhanal]

You can use table variable instead of temp table in a table valued function

[MAK]

create stored procedure that can run thru all the rows and parse the labname and insert into a temptable and return with "select * from temptable".

[timc]

Tried that, crystal does not like temp tables