CODEWITHSUNDEEP
sqlsql-server
xasx
xasx

Reputation: 11

Combining rows into one row

Table

id employee date
1 12345 2/1/2022
2 12345 2/6/2022

Results

employee date1 date2
12345 2/1/2022 2/6/2022

How can I write a SQL statement to combine dates from two rows into 1 row grouped by employee?

Upvotes: 0

Views: 46

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280645

-- if there are only two rows per employee:

SELECT employee, date1 = MIN(date), date2 = MAX(date)
  FROM dbo.two_rows_only
  GROUP BY employee;

-- if there can be more than two rows per employee you can determine which one you want. I assume the two oldest dates:

;WITH x AS
(
  SELECT employee, date, rn = ROW_NUMBER() 
    OVER (PARTITION BY employee ORDER BY date)
  FROM dbo.two_rows_only
)
SELECT employee, date1 = [1], date2 = [2]
FROM x
PIVOT 
(
  MAX(date) FOR rn IN ([1],[2])
) AS p;

-- example db<>fiddle

Upvotes: 3

Related Questions