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

Thank you, David!

-----Original Message-----
From: David McAfee <davidmcafee at gmail.com>
To: Access Developers discussion and problem solving<accessd at databaseadvisors.com>
Date: Thu, 26 Feb 2009 14:30:32 -0800
Subject: Re: [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
> >
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>