SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Breaking down ranges
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jayram
Starting Member

47 Posts

Posted - 03/25/2013 :  15:16:17  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 03/26/2013 :  14:44:51  Show Profile  Reply with Quote
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 - 03/27/2013 :  10:14:53  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 03/28/2013 :  05:45:33  Show Profile  Reply with Quote

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



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

Go to Top of Page

jayram
Starting Member

47 Posts

Posted - 04/05/2013 :  13:01:32  Show Profile  Reply with Quote
Thank you visakh
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 04/09/2013 :  01:17:52  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000