October 18, 2012 at 2:56 am
I am having a query i need to split the query based on the position i specify
Say
string is 12345678910111213141516........
i am having position as 1,1,2,5,4,2
i need as
1
2
34
56789
1011
12
Thanks
October 18, 2012 at 3:45 am
USE BELOW CODE...
--DROP TABLE #temp1
declare @STR varchar(100)='12345678910111213141516'
declare @pos varchar(50)='1,1,2,5,4,2'
declare @t int=0
create table #temp1(id varchar(100))
WHILE CHARINDEX(',',@pos)>0
BEGIN
set @t=SUBSTRING(@pos,1,(CHARINDEX(',',@pos)-1))
INSERT INTO #temp1 VALUES( substring(@str,1,@t))
SET @pos=SUBSTRING(@pos,(CHARINDEX(',',@pos))+1,LEN(@pos))
set @STR=SUBSTRING(@str,(@t+1),len(@str))
END
INSERT INTO #temp1 VALUES (SUBSTRING(@str,1,CONVERT(INT,@pos)))
--INSERT INTO @T1 VALUES(@VAL)
SELECT * FROM #temp1
October 18, 2012 at 4:52 am
hey it has worked thanks
now the thing is i need to insert this data into a table
my table is
create testtable (Rid int identity(1,1),col1 int,col2 int,col3 int,col4 int,col5 int,col6 int)
i need to insert this data i this table like
insert into testtable values ........
and my result from this table will be like something
Select * from testtable
Rid,col1,col2,col3,col4,col5,col6
1 ,1,2,34,56789,1011,12
Thanks
October 18, 2012 at 6:38 am
I have used Pivot pls correct me if i have done wrong
DECLARE @cols NVARCHAR(2000)
SELECT @cols = COALESCE(@cols + ',[' + Convert(varchar,Rid )+ ']', '[' + Convert(varchar,Rid ) + ']')
FROM #Temp
DECLARE @query NVARCHAR(4000)
SET @query = N'SELECT '+
@cols +'FROM (SELECT [Rid],id FROM #Temp) p
PIVOT
(min([id]) FOR [Rid] IN ( '+@cols +' )) AS pvt'
print @query
EXECUTE(@query)
October 19, 2012 at 2:35 am
Without while loop can we do this?
since loop is taking more time any other alternate method
Thanks!
October 19, 2012 at 4:02 am
Hi
I am getting the error as
declare @pi_input varchar(max)
declare @query varchar(max)
select @pi_input ='0123456'
SET @query = N'Select '+'SUBSTRING(@Input,'+convert(varchar,0) +',1)'
print @query
exec (@query)
Select SUBSTRING(@Input,0,1)
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@Input".
how to resolve it
thanks!
October 19, 2012 at 8:55 am
yuvipoy (10/19/2012)
HiI am getting the error as
declare @pi_input varchar(max)
declare @query varchar(max)
select @pi_input ='0123456'
SET @query = N'Select '+'SUBSTRING(@Input,'+convert(varchar,0) +',1)'
print @query
exec (@query)
Select SUBSTRING(@Input,0,1)
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@Input".
how to resolve it
thanks!
Not sure what this has to do with your original question but your dynamic sql is nowhere close here. You have @Input in your dynamic sql. That variable MUST be declared inside the dynamic sql. That variable is not declared outside your dynamic sql either so I am not sure what that is. Honestly from you posted I don't see any reason for dynamic sql at all.
_______________________________________________________________
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/
October 19, 2012 at 9:33 am
This looked like an extremely odd situation but a fun challenge. It can in fact be accomplished without loops. You have to combine a couple of techniques, both the delimited split and the quirky update.
You need to read the article referenced in my signature about splitting strings. In there you will find the code for the DelimitedSplit8K function.
Then you need to read this article about running totals. http://www.sqlservercentral.com/articles/T-SQL/68467/[/url]
Why running totals? I use the running total to keep track of the length of the string at the time of each split.
Make sure that you read and understand the concepts in both articles. These are pretty advanced topics and you are the one who has to support this code.
declare @SomeString varchar(50) = '12345678910111213141516'
declare @Pos varchar(50) = '1,1,2,5,4,2'
--For the quirky update we need this data in a table of some sort.
create table #StringOutput
(
RowNum int,
ColLength int,
SumOfLength int
)
insert #StringOutput
select *, null --The null will be updated with the running total
from dbo.DelimitedSplit8K(@pos, ',')
declare @Total int = 0
--this is the quirky update used to store the running total
update #StringOutput
set @Total = SumOfLength = @Total + ColLength
OPTION (MAXDOP 1)
--now that we know where in the original string and the length of each segment we just need to get the correct substring for each segment.
select *, substring(@SomeString, SumOfLength - ColLength + 1, ColLength) as FinalOutput
from #StringOutput
drop table #StringOutput
_______________________________________________________________
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/
October 22, 2012 at 12:13 am
Hi, thanks i have learned one new thing today,
but the thing is i need to insert into the testtable
Select * from testtable
Rid,col1,col2,col3,col4,col5,col6
1 ,1,2,34,56789,1011,12
my testtable will be a dynamic one based on the input of the position in my example i have given
declare @STR varchar(100)='12345678910111213141516'
declare @pos varchar(50)='1,1,2,5,4,2'
@pos may increase or decrease if it increase i need to create my testtable accordingly ,here it is 6 splits so i am having 6 columns later i may get 15 columns so i need to create a testtable with 15 columns and so on and i need to insert the data vertically after splitting.
October 22, 2012 at 3:18 am
yuvipoy (10/18/2012)
hey it has worked thanksnow the thing is i need to insert this data into a table
my table is
create testtable (Rid int identity(1,1),col1 int,col2 int,col3 int,col4 int,col5 int,col6 int)
i need to insert this data i this table like
insert into testtable values ........
and my result from this table will be like something
Select * from testtable
Rid,col1,col2,col3,col4,col5,col6
1 ,1,2,34,56789,1011,12
Thanks
The following query will meet this requirement:
DECLARE @STR VARCHAR(100) = '12345678910111213141516'
DECLARE @pos VARCHAR(50) = '1,1,2,5,4,2'
;WITH
-- resolve @pos into a table
ResolvedString AS (
SELECT
ItemNumber,
Item = CAST(Item AS INT)
FROM dbo.DelimitedSplit8K(@pos,',')
)
-- crosstab query
SELECT
IDENTITY (int, 1, 1) AS Rid,
Col1 = MAX(CASE WHEN r.ItemNumber = 1 THEN y.Word ELSE NULL END),
Col2 = MAX(CASE WHEN r.ItemNumber = 2 THEN y.Word ELSE NULL END),
Col3 = MAX(CASE WHEN r.ItemNumber = 3 THEN y.Word ELSE NULL END),
Col4 = MAX(CASE WHEN r.ItemNumber = 4 THEN y.Word ELSE NULL END),
Col5 = MAX(CASE WHEN r.ItemNumber = 5 THEN y.Word ELSE NULL END),
Col6 = MAX(CASE WHEN r.ItemNumber = 6 THEN y.Word ELSE NULL END)
INTO #Temp
FROM ResolvedString r
-- use a triangular join to calculate the start position for SUBSTRING()
CROSS APPLY (
SELECT Startpos = 1+ISNULL(SUM(ir.Item),0)
FROM ResolvedString ir
WHERE ir.ItemNumber < r.ItemNumber
) x
CROSS APPLY (
SELECT Word = SUBSTRING(@str,x.Startpos,r.Item)
) y
SELECT * FROM #Temp
Couple of questions for you:
Where do the input string and the positions string come from? How are they created?
What's downstream from the new table?
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 22, 2012 at 3:20 am
yuvipoy (10/22/2012)
@pos may increase or decrease if it increase i need to create my testtable accordingly ,here it is 6 splits so i am having 6 columns later i may get 15 columns so i need to create a testtable with 15 columns and so on and i need to insert the data vertically after splitting.
Can you provide some more information about the whole process?
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 22, 2012 at 3:55 am
Starting from my first post i will cont.. here
I am having a query i need to split the query based on the position i specify
Say
string is 12345678910111213141516........
i am having position as 1,1,2,5,4,2
i need as
1
2
34
56789
1011
12
string is 12345678910111213141516........ which will be input for me.
i am having position as 1,1,2,5,4,2 i am having this in a table
say table will look like
Splitter table
Rid , Delimiter
1,1
2,1
3,2
4,5
5,4
6,2
in the CTE example you have hard coded Col1,Col2..Col6 but this will be more or less for me for some time so i can't hardcoded as Col1..Col6 since for some time i may have splitter table to take only 4 values which means
Rid , Delimiter
1,1
2,1
3,2
4,5
for this input string
string is 12345678910111213141516........
rest i can ignore
Rid , Delimiter
1,1
2,1
for the same input string
string is 12345678910111213141516........
rest i can ignore
Rid , Delimiter
1,1
2,1
3,2
4,5
5,4
6,2
7,2
for the same input string
string is 12345678910111213141516........
the above statement is what i am saying as dynamic based on the split condition
Thanks!
October 22, 2012 at 4:33 am
yuvipoy (10/22/2012)
Starting from my first post i will cont.. here...
We know all of this already. What can you tell us about
1. The process which generates these two strings - where do they come from?
2. How the data is consumed, once you have created a table with x colums and y rows.
Thanks
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 22, 2012 at 4:56 am
We know all of this already. What can you tell us about
1. The process which generates these two strings - where do they come from?
2. How the data is consumed, once you have created a table with x colums and y rows.
Thanks
1) @string is the input from the code which will be passed to stored procedure
and table splitter is a default one there will not be any change in the column except we take some certain columns based on the third parameter , the third parameter will tell us what is the column we need to take from splitter table(eg: 6 or 4 or 2 or 7....) third parameter will tell us the splitter details.
2) the data is pre generated before the start of the process for the splitter table
Thanks
October 22, 2012 at 5:08 am
yuvipoy (10/22/2012)
We know all of this already. What can you tell us about
1. The process which generates these two strings - where do they come from?
2. How the data is consumed, once you have created a table with x colums and y rows.
Thanks
1) @string is the input from the code which will be passed to stored procedure
and table splitter is a default one there will not be any change in the column except we take some certain columns based on the third parameter , the third parameter will tell us what is the column we need to take from splitter table(eg: 6 or 4 or 2 or 7....) third parameter will tell us the splitter details.
2) the data is pre generated before the start of the process for the splitter table
Thanks
What generates @string? What generates @pos?
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
Viewing 15 posts - 1 through 15 (of 35 total)
You must be logged in to reply to this topic. Login to reply