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]