Help Needed in Tally Table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help Needed in Tally Table

Author

Topic

toprprasad
Starting Member

2 Posts

Posted - 2009-12-24 : 10:10:31

Guys:

--DDL Script
CREATE TABLE [dbo].[Tally]([N] [int] NOT NULL,
CONSTRAINT [PK_Tally1_N] PRIMARY KEY CLUSTERED
([N] ASC)) ON [PRIMARY]

--Insert into Tally
insert into Tally (N) select top 500 ROW_NUMBER() OVER(ORDER BY b.id DESC) AS 'Row Number' from sys.sysobjects a cross join sys.syscolumns b

---WIth tally table, following code works well..

DECLARE @Parameter VARCHAR(8000)
SET @Parameter = '1,2,3'

SELECT
SUBSTRING(','+@Parameter+',',N+1,CHARINDEX(',',','+@Parameter+',',N+1)-N-1) AS Value
FROM dbo.Tally WHERE
N < LEN(','+@Parameter+',')
AND SUBSTRING(','+@Parameter+',',N,1) = ','

OUTPUT is
1
2
3

But My requirement is to have split two parameters in one select statement, Both the parameters will have same number of delimiters(,). example below..

DECLARE @Parameter VARCHAR(8000)
SET @Parameter = '2221,221,321'

DECLARE @Parameter1 VARCHAR(8000)
SET @Parameter1 = 'a,b,c'

expected OUTPUT

2221 a
221 b
321 c

can anyone rewrite above code to have the values of these two parameters in two columns of select statement?

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2009-12-24 : 10:32:49

This approach uses ROW_NUMBER to produce a joining value...


SELECT 
	p1.Value,
	p2.Value
FROM
	(		
		SELECT 
			ROW_NUMBER () OVER (ORDER BY N ) AS 'id',
			SUBSTRING(','+@Parameter+',',N +1,CHARINDEX(',',','+@Parameter+',',N +1)-N -1) AS Value 
		FROM 
			dbo.Tally 
		WHERE 
			N  < LEN(','+@Parameter+',') 
			AND SUBSTRING(','+@Parameter+',',N ,1) = ','
	) p1
JOIN	
	(		
		SELECT 
			ROW_NUMBER () OVER (ORDER BY N ) AS 'id',
			SUBSTRING(','+@Parameter1+',',N +1,CHARINDEX(',',','+@Parameter1+',',N +1)-N -1) AS Value 
		FROM 
			dbo.Tally  
		WHERE 
			N  < LEN(','+@Parameter1+',') 
			AND SUBSTRING(','+@Parameter1+',',N ,1) = ','
	) p2 ON p2.id = p1.id

toprprasad
Starting Member

2 Posts

Posted - 2009-12-24 : 10:38:45

Thanks a lot......It Works great........

quote:

Originally posted by ehorn

This approach uses ROW_NUMBER to produce a joining value...


SELECT 
	p1.Value,
	p2.Value
FROM
	(		
		SELECT 
			ROW_NUMBER () OVER (ORDER BY N ) AS 'id',
			SUBSTRING(','+@Parameter+',',N +1,CHARINDEX(',',','+@Parameter+',',N +1)-N -1) AS Value 
		FROM 
			dbo.Tally 
		WHERE 
			N  < LEN(','+@Parameter+',') 
			AND SUBSTRING(','+@Parameter+',',N ,1) = ','
	) p1
JOIN	
	(		
		SELECT 
			ROW_NUMBER () OVER (ORDER BY N ) AS 'id',
			SUBSTRING(','+@Parameter1+',',N +1,CHARINDEX(',',','+@Parameter1+',',N +1)-N -1) AS Value 
		FROM 
			dbo.Tally  
		WHERE 
			N  < LEN(','+@Parameter1+',') 
			AND SUBSTRING(','+@Parameter1+',',N ,1) = ','
	) p2 ON p2.id = p1.id

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources