I'd like to number the rows created by using cross apply. Lets say I have a table like this
key|value
---+-----
1 | A
2 | B
and I run
select * from t CROSS apply String_Split('x,y', ',')
Id like to get that result:
key|value|value|number
---+-----+-----+--------
1 | A | x | 1
1 | A | y | 2
2 | B | x | 1
2 | B | y | 2
But I have no idead how to achive this; Also the "number" should be resitant to ordering. Any Hints? Suggestions?
Thanks!
Just another option if 2016+, is to use the JSON KEY. It would be a small matter to +1 on the KEY if needed
Example
Declare @YourTable Table ([id] int,[value] varchar(50))
Insert Into @YourTable Values
(1,'A')
,(2,'B')
Select A.*
,B.*
From @YourTable
Cross Apply (
SELECT [Key]
,[value]
FROM OPENJSON('["'+replace('x,y',',','","')+'"]')
) B
Returns
id value Key value
1 A 0 x
1 A 1 y
2 B 0 x
2 B 1 y
EDIT XML / 2014 version
Select A.*
,B.*
From @YourTable A
Cross Apply (
Select RetSeq = row_number() over (order by 1/0)
,RetVal = ltrim(rtrim(B.i.value('(./text())[1]', 'varchar(max)')))
From (Select x = Cast('<x>' + replace('x,y',',','</x><x>')+'</x>' as xml)) as A
Cross Apply x.nodes('x') AS B(i)
) B
You can also use a CTE. On the CTE I have added the string part number using row_number() over a constant (so it doesn't order by any column and generates the numbers in the original natural order).
with string_values as (
select row_number() over (order by (select 0)) as number,
value
from string_split('X,Y', ',')
)
select * from t cross join string_values
PS: Search for SQL Split String and you will find plenty of functions to replace split_string in SQL Server 2014.
如果觉得我的文章对您有用，请随意打赏。你的支持将鼓励我继续创作！
