How to collapse into a single row from multiple rows

Question

Please refer to the attached screenshot for the requirement. I have to collapse rows into a single row with latest test_date in one column and second_latest_date in an other column and latest order_number.

Please share the tips to create a SQL Server query that can (a) transform data from the Source table to (b) data as shown in the Target table.

Data sample for the Source table:

enter image description here

Data sample for the Target table:

enter image description here

Answer 1

You should be able to use a common table expression along with the ROW_NUMBER and LEAD windowing functions to help with this problem.

--demo setup based on your sample data
Declare @T table(DIV VARCHAR(20), LS INT, TT VARCHAR(5), TN INT, LAT DECIMAL(7,3), LONG DECIMAL(7,3), [DATE] DATETIME, ORDER_NUM VARCHAR(30))
INSERT INTO @T(DIV, LS, TT, TN, LAT, LONG, [DATE], ORDER_NUM) VALUES
('BRIDGE',4,'M',1,128.2,186.638, '2/11/2019 17:58', '1004085730'),
('BRIDGE',4,'M',1,128.2,186.638, '2/13/2019 15:22', '1004085755'),
('BRIDGE',4,'M',1,128.2,186.638, '2/17/2019 14:22', '1004104405'),
('CROSS',4,'M',1,170.4,190.2, '2/1/2019 14:22', '1004104405'),
('CROSS',4,'M',1,170.4,190.2, '2/10/2019 14:22', '1004104520'),
('CROSS',4,'M',1,170.4,190.2, '2/17/2019 14:22', '1004104590')
--the query
;WITH CTE
AS (
 SELECT *
 ,ROW_NUMBER() OVER (
 PARTITION BY DIV ORDER BY [DATE] DESC
 ) AS rn
 ,LEAD([date]) OVER (
 PARTITION BY div ORDER BY [date] DESC
 ) AS prev
 FROM @T
 )
SELECT div,ls,tt,tn,lat,long,[date] AS Latest_Test_Date,prev AS Second_Latest_Dte,ORDER_NUM
FROM CTE
WHERE rn = 1

---

| div | ls | tt | tn | lat | long | Latest_Test_Date | Second_Latest_Dte | ORDER_NUM |
|--------|----|----|----|---------|---------|-------------------------|-------------------------|------------|
| BRIDGE | 4 | M | 1 | 128.200 | 186.638 | 2019年02月17日 14:22:00.000 | 2019年02月13日 15:22:00.000 | 1004104405 |
| CROSS | 4 | M | 1 | 170.400 | 190.200 | 2019年02月17日 14:22:00.000 | 2019年02月10日 14:22:00.000 | 1004104590 |

---

By selecting directly from the common table expresssion SELECT * FROM CTE

| DIV | LS | TT | TN | LAT | LONG | DATE | ORDER_NUM | rn | prev |
|--------|----|----|----|---------|---------|-------------------------|------------|----|-------------------------|
| BRIDGE | 4 | M | 1 | 128.200 | 186.638 | 2019年02月17日 14:22:00.000 | 1004104405 | 1 | 2019年02月13日 15:22:00.000 |
| BRIDGE | 4 | M | 1 | 128.200 | 186.638 | 2019年02月13日 15:22:00.000 | 1004085755 | 2 | 2019年02月11日 17:58:00.000 |
| BRIDGE | 4 | M | 1 | 128.200 | 186.638 | 2019年02月11日 17:58:00.000 | 1004085730 | 3 | NULL |
| CROSS | 4 | M | 1 | 170.400 | 190.200 | 2019年02月17日 14:22:00.000 | 1004104590 | 1 | 2019年02月10日 14:22:00.000 |
| CROSS | 4 | M | 1 | 170.400 | 190.200 | 2019年02月10日 14:22:00.000 | 1004104520 | 2 | 2019年02月01日 14:22:00.000 |
| CROSS | 4 | M | 1 | 170.400 | 190.200 | 2019年02月01日 14:22:00.000 | 1004104405 | 3 | NULL |

you can see that the rn=1 rows are the latest row for each DIV. The LEAD window function looks to the row just before the latest row for that DIV to pick up the second_latest_date.

---

Update: based on your comment about wanting to union multiple tables before applying the windowing functions, you could use an inline table expression like this:

;WITH CTE
AS (
 SELECT *
 ,ROW_NUMBER() OVER (
 PARTITION BY DIV ORDER BY [DATE] DESC
 ) AS rn
 ,LEAD([date]) OVER (
 PARTITION BY div ORDER BY [date] DESC
 ) AS prev
 FROM 
 (
 select cols from TableOne
 UNION
 select cols from TableTwo
 ) as t
 )