yuvipoy (11/1/2012)

---

can we add

startid, endid SplitLayout

startid, endid
1,1
2,1
3,1
4,2
6,3
9,1
10,5
15,4
19,3
22,4

Startid = will start from 1 and will add endpoint for second row 1+1 =2 will be starting point for second row 2+1=3 will be starting point for third row 3+1=4 will be starting point for fourth row 4+2=6 will be starting point for fifth row and so on ........


;with t1 as( select '1010' st ),
t2 as( Select startid, endid from SplitLayout),
t3 as ( select st,startid s,Endid e, row_number() over(order by startid) rn from t1,t2)
select * from t3
pivot
(
max(substring(st,s,e)) str for rn in ([1],[2],[3])
) AS pvt

i am getting error near substring( open bracket;

Thanks!

yuvipoy (11/3/2012)

---

Any suggestions!

startid, endid
1,1
2,1
3,1
4,2
6,3
9,1
10,5
15,4
19,3
22,4

Startid = will start from 1 and will add endpoint for second row 1+1 =2 will be starting point for second row 2+1=3 will be starting point for third row 3+1=4 will be starting point for fourth row 4+2=6 will be starting point for fifth row and so on ........

yuvipoy (11/4/2012)

---

Say i'm having a string like this '101011123'
i need to split the sting based on my statid & endid

startid, endid
1,1
2,1
3,1
4,2
6,3

which needs to split something like
1,1---->1 starting position 1 and ending position 1 ,next starting id will be 1+1
2,1---->0 starting position 2 and ending position 1 ,next starting id will be 2+1
3,1---->1 starting position 3 and ending position 1 ,next starting id will be 3+1
4,2---->01 starting position 4 and ending position 2 ,next starting id will be 4+2
6,3---->112 starting position 6 and ending position 1 ,next starting id will be 6+3
9,.
.
.
.

yuvipoy (11/5/2012)

---

No, i am looking alternate method for pivot.
i have used another way like

DECLARE @input varchar(4000);
DECLARE @SQL NVARCHAR(MAX)
select @input='101011123'
SELECT @SQL = ISNULL(@SQL+N','+CHAR(10),'') +
+ N'Col' + RIGHT('0'+CAST(Rid AS NVARCHAR(10)),3) + N'='+''''+CONVERT(varchar(50),
SUBSTRING(@input,(Startid ),(Endid )))+'''' FROM mytable
SELECT @SQL = N'SELECT' + CHAR(10) + @SQL
EXEC (@SQL)

where as it is taking more time ,so looking for alternate way!