Other solution instead of Cursoring

Question

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

I don't see the relationship between the current and the proposed. — OMG Ponies, May 28 '10 at 00:37
@Thomas: I've updated the relationship between current and proposed one. Technically I can have more than 1 relationship (RID) and the PROPOSED one can be more than 2 per RID BUT it can only be 1 Current per RID. — dcpartners, May 28 '10 at 00:45

score 1 · Answer 1 · edited May 23 '17 at 12:19

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'

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

edited May 23 '17 at 12:19

Community

1
1

answered May 28 '10 at 00:49

thomas

2,592
1
30
44

Will do that approach. Thanks – dcpartners May 28 '10 at 00:50

Thomas · Accepted Answer · 2010-05-28T04:05:53.457

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

I am trying to understand this query. I don't quite undertand why this NUmbers do? The sequence seems not right though. In my query returned the number of sequence records for 'PROPOSED' so in my case I will have 4 (1, 2, 3, 4). Isn't that to be just 2 because it's grouping by NTRIId ? — dcpartners, May 28 '10 at 01:47
@dewacorp.alliances- I've revised my query slightly. The `Numbers` table is just a sequential list of integers. It's providing an equivalent sequence for the "current" values as is being created for the "proposed" values. I could have instead created a Cross Join to the revised ProposedSequences CTE. In short, I need a "Current" row for every sequence in ProposedSequences. — Thomas, May 28 '10 at 04:10

score 1 · Answer 3 · answered May 28 '10 at 09:58

1

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

answered May 28 '10 at 09:58

msi77

1,602
1
11
10

Thanks for this query. It's much shorter but it makes my head spinning a bit to understand this. – dcpartners Jun 01 '10 at 03:35
I used the combination of CTE and this query and work nicely. Thanks – dcpartners Jun 01 '10 at 04:39
hi @msi77: I've just found that this query it's only working if the number of proposed = 2 (NTRIId) BUT if it's more than 2, it's not really. Any ideas? – dcpartners Jun 15 '10 at 06:29

Other solution instead of Cursoring

3 Answers3

Linked