Other solution instead of Cursoring

Question

Hi there

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; 100; 0.1
1; CURRENT; 4; NonPeak; 200; 0.2

1; PROPOSED; 6; Peak; 100; 0.2
1; PROPOSED; 6; NonPeak; 200; 0.3

1; PROPOSED; 8; Peak; 200; 0.3
1; PROPOSED; 8; NonPeak; 200; 0.5

As you can see there is 2 sets of proposed (ID=6 and 8). I want somehow display like this below so each set has a pair of CURRENT as well as the PROPOSED one as follow:

**Sequence; RID; NTRITCode; NTRIId; Parameter; Usage; Rate**

1; 1; CURRENT; 4; Peak; 100; 0.1
1; 1; CURRENT; 4; NonPeak; 200; 0.2
1; 1; PROPOSED; 6; Peak; 100; 0.2
1; 1; PROPOSED; 6; NonPeak; 200; 0.3

2; 1; CURRENT; 4; Peak; 100; 0.1
2; 1; CURRENT; 4; NonPeak; 200; 0.2
2; 1; PROPOSED; 8; Peak; 200; 0.3
2; 1; PROPOSED; 8; NonPeak; 200; 0.5 

Again all I can think off is using combination of CURSOR and UNION but is there any TSQL that can do this?

Thanks

Answer 1

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'

http://stackoverflow.com/questions/2925767/sql-how-to-get-all-the-distinct-characters-in-a-column-across-all-rows

Answer 2

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 
    (
    Select Row_Number() Over ( Order By C1.object_id ) As Value
    From sys.columns As C1
        Cross Join sys.columns As C2
    )
    , ProposedSequences As
    (
    Select  NTRIId
        , Row_Number() Over ( Order By NTRIId ) As Sequence
    From Table
    Where NTRITCode = 'PROPOSED'
    Group By NTRIId
    )
    , CurrentSequences As
    (
    Select RID, NTRITCode, NTRIId, Parameter, Usage, Rate
        , Numbers.Value As Sequence
    From Table
        Cross Join Numbers
    Where NTRITCode = 'Current'
        And Numbers.Value <= (Select Max(Sequence) From ProposedSequence)
    )
Select Sequence, RID, NTRITCode, NTRIId, Parameter, Usage, Rate
From CurrentSequences
Union All
Select PS.Sequence, T.RID, T.NTRITCode, T.NTRIId, T.Parameter, T.Usage, T.Rate
From ProposedSequences As PS
    Join Table As T
        On T.NTRIId = PS.NTRIId
Order By PS.Sequence, T.NTRITCode

Answer 3

select row_number() over(PARTITION by c.NTRITCode,c.parameter order by c.NTRITCode, c.ntriid,c.parameter) nb, 
c.* from table c
join (select * from table where NTRITCode='PROPOSED') p 
on c.rid=p.rid and c.parameter=p.parameter
and not(p.NTRIId=c.NTRIId and c.parameter=p.parameter)
order by nb, c.ntriid, c.parameter desc