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)
 Breaking down ranges

Author  Topic 

jayram
Starting Member

47 Posts

Posted - 2013-03-25 : 15:16:17
hi

i have my data in a table with 2 columns. The 2 columns are like below. The table is based on an excel source.

e.g. Code1 Code2
11008 10180, 11004 – 11006

i would like to select like below, Code2 has both ranges and also comma separated

Should be like
Code1 Code2
11008 10180
11008 11004
11008 11005
11008 11006

do you think it is possible in SQL?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-26 : 14:44:51
its possible but you would require a string parsing technique like below

http://visakhm.blogspot.in/2010/02/parsing-delimited-string.html

see usage in scenario 4 here

http://visakhm.blogspot.in/2010/01/multipurpose-apply-operator.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

jayram
Starting Member

47 Posts

Posted - 2013-03-27 : 10:14:53
Thak you Visakh. i am using the UDF parsevalue and tested on the in the example and it works

declare @test table
(
CODE1 varchar(5),
CODE2 varchar(8000))

insert into @test select '20936', '22319, 22532, 22533, 22548-22558, 22590-22612, 22630, 22633, 22634, 22800-22812'
insert into @test select '20937', '22319, 22532, 22533, 22548-22558, 22590-22612, 22630, 22633, 22634, 22800-22812'

select * from @test

the above is my table and i was hoping to extract like below
select '20936', '22319'
select '20936', '22532'
select '20936', '22533'
select '20936', '22548'
select '20936', '22549'
select '20936', '22550'
select '20936', '22551'
select '20936', '22552'
...
...

can the UDF be modified and used to get to the result i want to?

Thanks


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-28 : 05:45:33
[code]
declare @test table
(
CODE1 varchar(5),
CODE2 varchar(8000))

insert into @test select '20936', '22319, 22532, 22533, 22548-22558, 22590-22612, 22630, 22633, 22634, 22800-22812'
insert into @test select '20937', '22319, 22532, 22533, 22548-22558, 22590-22612, 22630, 22633, 22634, 22800-22812'

SELECT p.CODe1,p.StartVal + v.number AS CODE2
FROM
(
SELECT t.CODE1,LEFT(f.Val,CHARINDEX('-',f.Val + '-')-1)*1 AS StartVal,
STUFF(f.Val,1,CHARINDEX('-',f.Val + '-'),'0')*1 AS EndVal
FROM @test t
CROSS APPLY dbo.ParseValues(t.CODE2,',') f
)p
CROSS JOIN master..spt_values v
WHERE v.number BETWEEN 0 AND COALESCE(NULLIF(EndVal,0),StartVal)-StartVal
AND v.type='p'


output
------------------------------
CODE1 CODE2
20936 22319
20936 22532
20936 22533
20936 22548
20936 22549
20936 22550
20936 22551
20936 22552
20936 22553
20936 22554
20936 22555
20936 22556
20936 22557
20936 22558
20936 22590
20936 22591
20936 22592
20936 22593
20936 22594
20936 22595
20936 22596
20936 22597
20936 22598
20936 22599
20936 22600
20936 22601
20936 22602
20936 22603
20936 22604
20936 22605
20936 22606
20936 22607
20936 22608
20936 22609
20936 22610
20936 22611
20936 22612
20936 22630
20936 22633
20936 22634
20936 22800
20936 22801
20936 22802
20936 22803
20936 22804
20936 22805
20936 22806
20936 22807
20936 22808
20936 22809
20936 22810
20936 22811
20936 22812
20937 22319
20937 22532
20937 22533
20937 22548
20937 22549
20937 22550
20937 22551
20937 22552
20937 22553
20937 22554
20937 22555
20937 22556
20937 22557
20937 22558
20937 22590
20937 22591
20937 22592
20937 22593
20937 22594
20937 22595
20937 22596
20937 22597
20937 22598
20937 22599
20937 22600
20937 22601
20937 22602
20937 22603
20937 22604
20937 22605
20937 22606
20937 22607
20937 22608
20937 22609
20937 22610
20937 22611
20937 22612
20937 22630
20937 22633
20937 22634
20937 22800
20937 22801
20937 22802
20937 22803
20937 22804
20937 22805
20937 22806
20937 22807
20937 22808
20937 22809
20937 22810
20937 22811
20937 22812

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

jayram
Starting Member

47 Posts

Posted - 2013-04-05 : 13:01:32
Thank you visakh
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-09 : 01:17:52
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -