Split - 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)

Split

Author

Topic

tawnie
Starting Member

2 Posts

Posted - 2009-12-23 : 01:59:59

Hi,

I would like to have a syntax to convert the following data into rows. Ok i have columns as below.

Doc Batch
1 1-5
2 6-10

i want an output as below

Doc Batch
1 1
1 2
1 3
1 4
1 5
2 6
2 7
2 8
2 9
2 10

please assist me with a code to give me the above output

Regards

tawnie

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-12-23 : 02:35:48

[code]
DECLARE @t TABLE (doc INT, batch VARCHAR(32))

INSERT INTO @t SELECT 1,'1-5'
INSERT INTO @t SELECT 2,'6-10'
INSERT INTO @t SELECT 3,'15-17'

SELECT t.*,LEFT(batch,CHARINDEX('-',batch)-1)+number
FROM @t t
INNER JOIN master..spt_Values m ON m.type = 'p'
WHERE LEFT(batch,CHARINDEX('-',batch)-1)+number <= RIGHT(batch,LEN(batch)-CHARINDEX('-',batch))
[/code]

tawnie
Starting Member

2 Posts

Posted - 2009-12-23 : 06:08:02

hi i did my query as below;

SELECT OWTR.DocNum,
left(IBT1.BatchNum,charindex('-',IBT1.BatchNum)-1) as Batch1,
RIGHT(IBT1.BatchNum,LEN(IBT1.BatchNum)-CHARINDEX('-',IBT1.BatchNum)) as Batch2
FROM OWTR INNER JOIN
WTR1 ON OWTR.DocEntry = WTR1.DocEntry INNER JOIN
IBT1 ON WTR1.DocEntry = IBT1.BaseEntry
WHERE (IBT1.BaseType = 67) AND (IBT1.Direction = '1')

i got 3 columns
DocNum Batch1 Batch2
1 1 5
2 6 10

now my aim is to return the below output
DocNum Batch1 Batch2 batch
1 1 5 1
1 1 5 2
1 1 5 3
1 1 5 4
1 1 5 5
2 6 10 6
2 6 10 7
2 6 10 8
2 6 10 9
2 6 10 10

please let me know how to handle this on my code

tawnie

ddramireddy
Yak Posting Veteran

81 Posts

Posted - 2009-12-23 : 06:30:05

declare @t table
(
Id int identity(1,1),
Batch1 int,
Batch2 int
)
insert into @t
select 1,5 union all
select 6,10

;with cte as
(
select ID,Batch1,Batch2,Batch1 as batch from @t
union all
select c.ID,c.Batch1,c.Batch2,batch + 1 from cte c
inner join @t t on c.Id= t.Id and batch < c.Batch2
)
select * from cte order by Id

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-12-23 : 06:33:56

DECLARE @t TABLE (doc INT, batch1 int, batch2 int)

INSERT INTO @t SELECT 1,1,5
INSERT INTO @t SELECT 2,6,10
INSERT INTO @t SELECT 3,15,17

SELECT t.*,batch1+number
FROM @t t
INNER JOIN master..spt_Values m ON m.type = 'p'
WHERE batch1+number <= batch2

ddramireddy
Yak Posting Veteran

81 Posts

Posted - 2009-12-23 : 06:43:44

quote:
Originally posted by bklr

DECLARE @t TABLE (doc INT, batch1 int, batch2 int)

INSERT INTO @t SELECT 1,1,5
INSERT INTO @t SELECT 2,6,10
INSERT INTO @t SELECT 3,15,17

SELECT t.*,batch1+number
FROM @t t
INNER JOIN master..spt_Values m ON m.type = 'p'
WHERE batch1+number <= batch2

More better approach than me.....

Hopefully, Batch Duration will be less than 2048... :)

and In my case also hopefullt the batch duration will be less than 32,768

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources