Sql-server – How to collapse into a single row from multiple rows

querysql serversql server 2014t-sql

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:

Data sample for the Target table:

Best Answer

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
    )

Related Solutions

Sql-server – Combine column from multiple rows into single row

This is relatively trivial to do with a correlated subquery. You can't use the COALESCE method highlighted in the blog post you mention unless you extract that to a user-defined function (or unless you only want to return one row at a time). Here is how I typically do this:

DECLARE @x TABLE 
(
  id INT, 
  row_num INT, 
  customer_code VARCHAR(32), 
  comments VARCHAR(32)
);

INSERT @x SELECT 1,1,'Dilbert','Hard'
UNION ALL SELECT 1,2,'Dilbert','Worker'
UNION ALL SELECT 2,1,'Wally','Lazy';

SELECT id, customer_code, comments = STUFF((SELECT ' ' + comments 
    FROM @x AS x2 WHERE id = x.id
     ORDER BY row_num
     FOR XML PATH('')), 1, 1, '')
FROM @x AS x
GROUP BY id, customer_code
ORDER BY id;

If you have a case where the data in comments could contain unsafe-for-XML characters (>, <, &), you should change this:

     FOR XML PATH('')), 1, 1, '')

To this more elaborate approach:

     FOR XML PATH(''), TYPE).value(N'(./text())[1]', N'varchar(max)'), 1, 1, '')

(Be sure to use the right destination data type, varchar or nvarchar, and the right length, and prefix all string literals with N if using nvarchar.)

Combine column from multiple rows into one row in SQLITE

You can use the GROUP_CONCAT() function:

SELECT a.Name               AS ArticleName
     , GROUP_CONCAT(t.Name) AS TagList
FROM Articles AS a
  LEFT JOIN ArticlesTags AS at
    ON at.ArticleId = a.Id
  LEFT JOIN Tags AS t
    ON t.Id = at.TagId
GROUP BY a.Id, a.Name ;

Best Answer

Related Solutions

Sql-server – Combine column from multiple rows into single row

Combine column from multiple rows into one row in SQLITE

Related Question