Note: T6 date always happens before T7.
One way of doing it is via pivot. For this solution I will assume that there is a table named SomeTable with 3 columns named Barcode, Date (lovely named after reserved keyword), and TransID. The script below is based on the assumption that for each barcode there might be multiple records which may be paired based on the date because for each T6 record there has to be T7 record before the next T6 record may appear. If the total number of records per barcode is odd then the last record is the T6 record. To easily see how the records are paired before pivoting, please highlight and run just the script inside of the src sub-select (this will reveal how each T6/T7 records are "paired"). Here is the complete script:
select Barcode, T7, T6 from ( select *, (row_number() over (partition by Barcode order by [Date]) - 1) / 2 + 1 n from SomeTable ) src pivot (max([Date]) for TransID in ([T6], [T7])) pvt order by Barcode, n;
Based on the sample data in question, the script produces the following results:
Barcode T7 T6 ------- ---------- ---------- A 2018-01-05 2018-01-01 A NULL 2018-01-10 B 2018-01-03 2018-01-01 C NULL 2018-01-03
Hope this helps.
21 People are following this question.