Copy Rows Based on Value

Thanks mister.magoo I'll have a read and see how I go.

ok, epic fail on my part. I'm not advanced enough with TSQL to be able to add based on a tally table where the tally is unique depending on the name.

Hi

You may also want to have a look at APPLY as well then

http://www.sqlservercentral.com/articles/APPLY/69953/

http://www.sqlservercentral.com/articles/APPLY/69954/

Using APPLY you could end up with something like the following

SELECT

-- Original data from table

t.Name,

t.Start,

t.[End],

t.Height,

-- Start + N from Tally offset by 1. This gives us the series

-- Start + 0 .. Start + (End - Start) for Position

t.Start + (x.N - 1) Position

FROM SomeTable t

CROSS APPLY ( -- For each row in Sometable do the following query

SELECT

-- Restrict the rows to the difference between start and end

-- plus 1 to allow for the start to be output as well.

-- If height is always the difference between End and Start

-- this could be used rather than calculating the difference.

TOP ((t.[End] - t.Start) + 1)

N

FROM Tally -- I have assumed that there is a tally table/view

) x;

Not exactly a tally table as mister.magoo mentioned but somewhat equivalent.

create table #t(person varchar(100),id1 int,id2 int,id3 int)

insert #t

select 'Person1', 10, 15, 5 union

select 'Person2', 14,14,0 union

select 'Person3', 20,21,1

select person,id1,id2,isnull(T.number,0)+id1 id3

from #t outer apply(select * from master..spt_values where number between

0 and id3 and type='P')T order by person

drop table #t

Kind of managed to get it working using a CTE

WITH CTE AS

(

Name,

StartPosition,

EndPosition,

'' AS Position,

Height

FROM MYTABLE

UNION ALL

Name,

StartPosition,

EndPosition,

(StartPosition + 1) AS Position

Height

FROM CTE

WHERE StartPosition < EndPositon

)

SELECT *

FROM CTE

You might want to be careful using recursive CTEs to do counting. They don't perform very well.

http://www.sqlservercentral.com/articles/T-SQL/74118/

If you don't want or can't create a Tally table you could always use a CTE to create an inline Tally table.

WITH

-- Ten values

E1 AS (SELECT 0 N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) E(N)),

-- 100 Values (10 x 10)

E2 AS (SELECT 0 N FROM E1 a, E1 b),

-- cteTally with 10000 Values (100 x 100)

cteTally AS (

SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) N

FROM E2 a, E2 b

)

SELECT

t.Name,

t. StartPosition,

t. EndPosition,

t.Height,

t.StartPosition+ (x.N - 1) Position

FROM MyTable t

CROSS APPLY (

SELECT

TOP ((t.EndPosition - t.StartPosition) + 1)

N

FROM cteTally

) x;

Also what you have posted looks like it will carry on recursing until the limit is hit.

If you want to stick with the recursive CTE you may want to try the following

WITH

CTE AS (

SELECT --Added in SELECT Clause

Name,

StartPosition,

EndPosition,

-- First time through set the position to startPosition

StartPosition AS Position,

Height

FROM MYTABLE

UNION ALL

SELECT --Added in SELECT Clause

Name,

StartPosition,

EndPosition,

-- Increment position

(Position + 1) AS Position,

Height

FROM CTE

WHERE

-- Only recurse where position is less than endPosition

Position < EndPosition

)

SELECT *

FROM CTE

ORDER BY NAME, Position

You may also want to have a look at the MAXRECURSION hint

http://msdn.microsoft.com/en-us/library/ms181714.aspx

Thanks for that.

I will stick with a CTE for now, as my data currently is only 400 rows post CTE function, well below the CTE limit. I have added the Order By clause as suggested.

Also in term of Tally Tables, I'm going to try and move my query to that, just little slow to catch on with how they work.

Thanks again for everyone's time.