CODEWITHSUNDEEP
sqlsql-servercsv
vijay sahu
vijay sahu

Reputation: 815

SQL Cross apply to rotate the comma separated values

declare @RuleTable table (RuleId int, Pattern varchar(max) , Frequency int )


insert into @RuleTable 
select 1, '3,5,4,6' , 2

 SELECT 
 ROW_NUMBER() OVER(PARTITION BY items ORDER BY (select 1)) - 1  AS Id,
 RuleId,
 items AS ShiftId 
FROM @RuleTable
    CROSS APPLY SplitNew(REPLICATE(REPLACE(','+ LTRIM(RTRIM(Pattern)) +',',',0,',',') +',', Frequency ), ',') as ss

current output is this...

enter image description here

but in expected output I should not lost the order of the comma separated string

Means Output should be like

ShiftId 
3
3
5
5
4
4
6
6

Note: Frequency means how many times each shiftid from comma separated string should repeat.

Upvotes: 0

Views: 277

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269583

You can find the first position of a value in the string:

SELECT ss.seqnum - 1  AS Id, RuleId, items AS ShiftId 
FROM @RuleTable CROSS APPLY
     (SELECT ss.*,
             ROW_NUMBER() OVER (ORDER BY CHARINDEX(',' + Frequency + ',', ',' + Pattern + ',')) as seqnum
      FROM SplitNew(REPLICATE(REPLACE(','+ LTRIM(RTRIM(Pattern)) +',', ',0,', ',') +',', Frequency ), ',') as ss
     ) ss;

I don't understand your search -- but then you are using an undocumented function. This would more typically be written as:

SELECT (ss.seqnum - 1) as Id, rt.RuleId, ss.value as ShiftId 
FROM @RuleTable rt CROSS APPLY
     (SELECT ss.*,
             ROW_NUMBER() OVER (ORDER BY CHARINDEX(',' + rt.Frequency + ',', ',' + rt.Pattern + ',')) as seqnum
      FROM string_split(pattern, ',') ss
     ) ss;

Note that this uses the built-in string_split() function. There is no need for a user-defined function with this method.

Upvotes: 1

Thom A
Thom A

Reputation: 95554

It appears you have a couple of problems here. Firstly, I would guess that your splitter (that we are missing the definition of) does not observe/retain ordinal position. Therefore we need to use a splitter than does. I recommend delimitedsplit8k_LEAD.

Then we need to look at how you repeat the rows. Using REPLICATE like that isn't the right approach. You'll be better off with a Tally. I've used an inline one here, and assume Frequency can't have a value greater than 10. If it does, use a CTE to create an inline Tally (there are plenty of examples out there on how to do this, and don't use the ones that use an rCTE, because they are slow).

After that, you end up with something like this:

SELECT ROW_NUMBER() OVER (PARTITION BY DS.Item ORDER BY DS.ItemNumber) - 1 AS ID,
       RT.RuleId,
       DS.Item
FROM @RuleTable RT
     CROSS APPLY dbo.DelimitedSplit8K_LEAD(RT.Pattern,',') DS
     JOIN (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10))V(I) ON RT.Frequency >= V.I 
ORDER BY DS.ItemNumber,
         V.I;

Upvotes: 2

Related Questions