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'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
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
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
