Other Solution Instead Of Cursoring

I have the following pivoting table that I manage to do and here's the result and I want to put a bit further. RID; NTRITCode; NTRIId; Parameter; Usage; Rate** 1; CURRENT; 4; Peak

Solution 1:

I'm not sure you really need a recursive query as much as a Numbers/Tally table in order to get the equivalent sequence numbers for the "CURRENT" values as those of the "PROPOSED" values.

With 
    Numbers As 
    (
    SelectRow_Number() Over ( OrderBy C1.object_id ) AsValueFrom sys.columns As C1
        CrossJoin sys.columns As C2
    )
    , ProposedSequences As
    (
    Select  NTRIId
        , Row_Number() Over ( OrderBy NTRIId ) As Sequence
    FromTableWhere NTRITCode ='PROPOSED'GroupBy NTRIId
    )
    , CurrentSequences As
    (
    Select RID, NTRITCode, NTRIId, Parameter, Usage, Rate
        , Numbers.Value As Sequence
    FromTableCrossJoin Numbers
    Where NTRITCode ='Current'And Numbers.Value <= (SelectMax(Sequence) From ProposedSequence)
    )
Select Sequence, RID, NTRITCode, NTRIId, Parameter, Usage, Rate
From CurrentSequences
UnionAllSelect PS.Sequence, T.RID, T.NTRITCode, T.NTRIId, T.Parameter, T.Usage, T.Rate
From ProposedSequences As PS
    JoinTableAs T
        On T.NTRIId = PS.NTRIId
OrderBy PS.Sequence, T.NTRITCode

Solution 2:

perhaps what mdma posted on this question may work for you. i have never thought of it but a recursive CTE is very interesting. The starting point could be number of distinct 'proposed pairs' and then it would union with itself as many times as necessary to create the right number of 'current pairs'

SQL: how to get all the distinct characters in a column, across all rows

Solution 3:

selectrow_number() over(PARTITIONby c.NTRITCode,c.parameter orderby c.NTRITCode, c.ntriid,c.parameter) nb, 
c.*fromtable c
join (select*fromtablewhere NTRITCode='PROPOSED') p 
on c.rid=p.rid and c.parameter=p.parameter
andnot(p.NTRIId=c.NTRIId and c.parameter=p.parameter)
orderby nb, c.ntriid, c.parameter desc