November 1, 2012 at 4:27 am
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!
November 1, 2012 at 7:40 am
ddl, sample data and desired output please. See the first link in my signature.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 1, 2012 at 7:53 am
yuvipoy (11/1/2012)
can we addstartid, 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!
Use a delimiter, you know how hard this excercise can become without one.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 1, 2012 at 10:31 pm
'1010' is the input i need to split the input based on the startid, endid
startid, endid
1,1
2,1
3,1
4,2
6,3
so i need something like
1,0,1,0,11,123,........
need to split the string and insert into the table.
November 3, 2012 at 3:43 am
Any suggestions!
November 3, 2012 at 11:41 am
yuvipoy (11/3/2012)
Any suggestions!
I can't speak for the others but I don't understand your narrative for this problem.
I recommend that you take a look at the article at the first link in my signature line to post some readily consumable data and try asking the question again. For example, I don't see how the narrative matches up with the data you posted for the following...
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 ........
--Jeff Moden
November 4, 2012 at 10:21 pm
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,.
.
.
.
November 5, 2012 at 2:13 am
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,.
.
.
.
That's startposition and length, and it's significantly easier to resolve the elements out of your target string using this than the original method you proposed on another thread. Elements would initially resolve into rows of a column, which leaves you with the same problem as you had on that other thread - the maximum number of columns in a row when you pivot. Have you decided what to do about this?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 5, 2012 at 5:38 am
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!
November 5, 2012 at 5:56 am
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!
How many different record layouts would you like to handle automatically?
And, no... this actually isn't a pivot problem. It's simply a split of fix field format data. I can help you after I get home from work tonight.
In the meantime, it would be helpful if you could provide some readily consumable test data. Please see the first link in my signature line below for the best way to do that. Thanks.
--Jeff Moden
November 5, 2012 at 11:45 pm
How many different record layouts would you like to handle automatically?
i am having some 80 columns
And, no... this actually isn't a pivot problem. It's simply a split of fix field format data. I can help you after I get home from work tonight.
i have tried with pivot as well as the method i have posted,it seems that the method posted above by me is more faster than pivot, i'm looking for more faster process then the existing pivot and other method.
In the meantime, it would be helpful if you could provide some readily consumable test data.
this is mytable
startid, endid
1,1
2,1
3,1
4,2
6,3
input will be '101011123'
i need like
Select 1,0,1,01,112
if your still unclear,given me your understanding on this post i will try to correct them :exclamationmark:
Thanks
November 6, 2012 at 2:58 am
DECLARE
@MyInput VARCHAR(8000),
@MyOutput VARCHAR(8000)
SELECT
@MyInput = '101011123',
@MyOutput = ''
;WITH MyTable (startid, Length) AS (
SELECT 1,1 UNION ALL
SELECT 2,1 UNION ALL
SELECT 3,1 UNION ALL
SELECT 4,2 UNION ALL
SELECT 6,3
)
-- resolve the string elements into rows of a column
-- then concatenate with a comma in between
SELECT @MyOutput = @MyOutput + ',' + SUBSTRING(@MyInput, m.startid, m.Length)
FROM MyTable m
-- remove the leading comma
SET @MyOutput = STUFF(@MyOutput,1,1,'')
SELECT MyOutput = @MyOutput
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 6, 2012 at 5:43 am
ChrisM@Work (11/6/2012)
DECLARE@MyInput VARCHAR(8000),
@MyOutput VARCHAR(8000)
SELECT
@MyInput = '101011123',
@MyOutput = ''
;WITH MyTable (startid, Length) AS (
SELECT 1,1 UNION ALL
SELECT 2,1 UNION ALL
SELECT 3,1 UNION ALL
SELECT 4,2 UNION ALL
SELECT 6,3
)
-- resolve the string elements into rows of a column
-- then concatenate with a comma in between
SELECT @MyOutput = @MyOutput + ',' + SUBSTRING(@MyInput, m.startid, m.Length)
FROM MyTable m
-- remove the leading comma
SET @MyOutput = STUFF(@MyOutput,1,1,'')
SELECT MyOutput = @MyOutput
You have given me the same method which i have used earlier
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)
November 6, 2012 at 6:40 am
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!
It runs too fast to register in milliseconds:
DECLARE @input varchar(4000);
DECLARE @SQL NVARCHAR(MAX)
select @input='101011123'
DECLARE @StartTime DATETIME
SET @StartTime = GETDATE()
;WITH MyTable (Rid, startid, Endid) AS (
SELECT 1,1,1 UNION ALL
SELECT 2,2,1 UNION ALL
SELECT 3,3,1 UNION ALL
SELECT 4,4,2 UNION ALL
SELECT 5,6,3
)
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
--print @SQL
EXEC (@SQL)
SELECT DATEDIFF(ms, @StartTime, GETDATE())
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 6, 2012 at 6:42 am
yuvipoy (11/5/2012)
this is mytablestartid, endid
1,1
2,1
3,1
4,2
6,3
Is this structure the same or different for each string of numbers you have to process?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply