[AccessD] OT: T-SQL 2005 - Building lookup rows from string...

Here's a udf that I stole from Francisco. I might have tweaked it a
bit, not sure. :)

CREATE  FUNCTION udfListToTable (@HList VarChar(1000), @Delimiter CHAR(1))
RETURNS @ListTable TABLE (Mystr VARCHAR(20))
AS
BEGIN
	--Purpose: To convert a Comma delimited text to a Temp Variable table
To help avoid dynamic sql
	--  Instead you can join the temp table or use it in your where
clause if a field is IN the subquery
        -- Call as such:  SELECT * FROM
dbo.udfListToTable('jim,joe,bob,tom',',')
	DECLARE @Mystrtext as VarChar(20)

	IF RIGHT(RTRIM(@HLIST),1) <>@Delimiter
		SET @HList = @HList + @Delimiter
	
	WHILE CHARINDEX(@Delimiter, @HList) > 0
	BEGIN
		IF CHARINDEX(@Delimiter, @HList) > 0
		   BEGIN
			SELECT @Mystrtext =LEFT(@HList, CHARINDEX(@Delimiter, @HList)-1)
		   END
		ELSE	
		   BEGIN
		 	SELECT @Mystrtext = RTRIM(LTRIM(@HList))
		   END
		--Insert into Variable Table
		INSERT INTO @ListTable(Mystr)
		       SELECT RTRIM(LTRIM(@Mystrtext))
		--Remove Item from list
		SELECT @HList = RIGHT(RTRIM(@HList), LEN(RTRIM(@HList)) -
CHARINDEX(@Delimiter, @HList))
	END	
	RETURN
END






2009/2/26 Salakhetdinov Shamil <mcp2004 at mail.ru>:
> Hi All,
>
> Using the following T-SQL in MS SQL 2005:
>
> select
> REPLACE(
> (
>  SELECT
>        Cast(CategoryId as nvarchar(10)) + ',' +
>        CategoryName + ','
>        FROM Categories
>        ORDER BY CategoryName
>        FOR XML PATH('')
> ) + '-',
> ',-','')
> as Categories
>
> You can get the string
>
> 1,Beverages,2,Condiments,3,Confections,4,Dairy Products,5,Grains/Cereals,6,Meat/Poultry,7,Produce,8,Seafood
>
> Questions:
> 1. If you know better way to get the same string please post it here (please do not spend time looking for it if youy do not have prompt solution from your experience - looking for other solution could be time consuming).
>
> 2. Do you know similar T-SQL only based way to split the above string to get a table:
>
> CategoryId CategoryName
> 1 Beverages
> 2 Condiments
> ...
> 8 Seafood
>
> ? (probably UDF should be used)
>
>
> N.B. The above technique introduced here (http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/)coudl be used to make cross-tab queries in T-SQL using dynamic SQL...
>
> Thank you.
>
> --
> Shamil
>
>
>
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>