September 21, 2010 at 2:04 pm
I have a table with source data containing several columns including name, and a string. The string is a list of keys numbers, separated with ':'. There is a function that analyzes the string and returns a table containing them.
Is there a way (besides using cursor, of course) to get a table that has name and key as columns, with the keys listed as rows?
Small sample code and data below:
CREATE FUNCTION dbo.ft_kulcs_lista
(
@kulcs_mezo VARCHAR(4000)
)
RETURNS @ret_table TABLE (kulcs_pozicio SMALLINT)
AS
BEGIN
DECLARE@temp VARCHAR(4000)
,@delimpos SMALLINT
,@kulcs_pozicio SMALLINT
SET @temp = @kulcs_mezo
IF PATINDEX('%[^0-9:]%' ,@kulcs_mezo) = 0
BEGIN
WHILELEN(@temp) > 0
BEGIN
SET @delimpos = CHARINDEX(':' ,@temp)
IF @delimpos > 0
SET @kulcs_pozicio = CAST(SUBSTRING(@temp ,1 ,@delimpos - 1) AS INT)
ELSE
BEGIN
SET @kulcs_pozicio = CAST(SUBSTRING(@temp ,1 ,LEN(@temp)) AS INT)
SET @temp = ''
END
IF NOT EXISTS ( SELECT 1 FROM @ret_table rt WHERErt.kulcs_pozicio = @kulcs_pozicio )
INSERT INTO @ret_table VALUES ( @kulcs_pozicio )
SET @temp = SUBSTRING(@temp ,@delimpos + 1 ,256)
END
END
RETURN
END
GO
DECLARE@source_data TABLE (nev VARCHAR(16) ,kulcsok VARCHAR(64))
INSERT INTO @source_data
SELECT 'Adam' ,'1:2:3'
UNION ALL
SELECT 'Bill' ,'2:3'
UNION ALL
SELECT 'Chuck' ,'4:7'
-- this code obviously does not work
SELECT
*
FROM
@source_data sd
,dbo.ft_kulcs_lista(sd.kulcsok) fkl
The expected result:
Adam,1
Adam,2
Adam,3
Bill,2
Bill,3
Chuck,4
Chuck,7
September 22, 2010 at 2:45 pm
SELECT *
FROM @source_data sd
CROSS APPLY dbo.ft_kulcs_lista(sd.kulcsok) fkl
Eddie Wuerch
MCM: SQL
September 22, 2010 at 3:00 pm
Unfortunately, I'm stuck with MS SQL 2000 (Desktop Engine). I found a note in Books Online (2008 R2) stating that "To use APPLY, the database compatibility level must be at least 90." That means it has to be at least on a 2005 server, am I correct?
While this does not seem to help me now, it could still come in handy some day, thanks.
September 22, 2010 at 4:08 pm
Correct. With SQL Server 2000, you cannot feed a TVF from the query itself.
Eddie Wuerch
MCM: SQL
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply