query help - 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

General SQL Server Forums

New to SQL Server Programming

query help

Author

Topic

kodumudisadha
Starting Member

33 Posts

Posted - 2009-03-05 : 08:00:38

hi,

in below query one row with fromnumber,tonumber i want to split between fromnumber and tonumber

select * from GVTSGVNUMBER_BNG

gvname fromnumber tonumber
green 12 15
blue 18 19
----

expected output:

gvname gvumber
green 12
green 13
green 14
green 15
blue 18
blue 19
----

How to do this .

please help

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-05 : 08:06:32

[code]DECLARE @Sample TABLE
(
gvName VARCHAR(20),
fromNumber TINYINT,
toNumber TINYINT
)

INSERT @Sample
SELECT 'green', 12, 15 UNION ALL
SELECT 'blue', 18, 19

SELECT s.gvName,
v.Number + fromNumber AS gvNumber
FROM @Sample AS s
INNER JOIN master..spt_values AS v ON v.Type = 'P'
WHERE v.Number <= toNumber - FromNumber[/code]

E 12°55'05.63"
N 56°04'39.26"

shaggy
Posting Yak Master

248 Posts

Posted - 2009-03-05 : 08:24:28

if this value is out of range...?

kodumudisadha
Starting Member

33 Posts

Posted - 2009-03-05 : 08:53:54

Hi,
my fromnumber and tonumber is varchar datatype so its showing
'Invalid operator for data type. Operator equals subtract, type equals varchar'

How to convert this query for varchar datatype?

please help.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-03-05 : 08:57:11

quote:
Originally posted by kodumudisadha

Hi,
my fromnumber and tonumber is varchar datatype so its showing
'Invalid operator for data type. Operator equals subtract, type equals varchar'

How to convert this query for varchar datatype?

please help.

Cast them to INT

CAST(fromnumber as INT)

Madhivanan

Failing to plan is Planning to fail

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-05 : 09:00:45

Unpivot should work as well.

DECLARE	@Sample TABLE
	(
		gvName VARCHAR(20),
		fromNumber TINYINT,
		toNumber TINYINT
	)

INSERT	@Sample
SELECT	'green', 12, 15 UNION ALL
SELECT	'blue', 18, 19

select gvname,number from (select * from @sample)a unpivot ( number for tag in(fromnumber,tonumber))a

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-03-05 : 09:03:54

quote:

Originally posted by sakets_2000

Unpivot should work as well.

DECLARE	@Sample TABLE
	(
		gvName VARCHAR(20),
		fromNumber TINYINT,
		toNumber TINYINT
	)

INSERT	@Sample
SELECT	'green', 12, 15 UNION ALL
SELECT	'blue', 18, 19

select gvname,number from (select * from @sample)a unpivot ( number for tag in(fromnumber,tonumber))a

Yes. But the result is not what OP wanted

Madhivanan

Failing to plan is Planning to fail

kodumudisadha
Starting Member

33 Posts

Posted - 2009-03-05 : 09:31:12

Thanks for all reply its working.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-05 : 12:34:22

IF sql 2005 you can do like this


;With CTE (gvname, fromnumber, tonumber)
AS
(
SELECT gvname, fromnumber, tonumber
FROM Table
UNION ALL
SELECT gvname, fromnumber+1, tonumber
FROM CTE
WHERE fromnumber+1<= tonumber
)

SELECT gvname, fromnumber
FROM CTE

OPTION (MAXRECURSION 0)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources