Numbering CROSS Apply rows

问题: 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 appl...

问题:

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!


回答1:

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

回答2:

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.

  • 发表于 2019-07-08 00:25
  • 阅读 ( 201 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除