CODEWITHSUNDEEP
sql-server
Bobby
Bobby

Reputation: 3

SQL Server Flatten Certain Columns

How can I flatten specific columns in a SQL Server query?

Data source:

AddressId | Address          | CreatedDate
   1      | 123 Main Street  | 2019-12-16
   1      | 123 Main Street  | 2019-12-15
   1      | 123 Main Street  | 2019-12-14
   2      | 456 South Street | 2019-12-13
   2      | 456 South Street | 2019-12-12
   2      | 456 South Street | 2019-12-11

Desired results:

AddressId | Address          | CreatedDate0 | CreatedDate1 | CreatedDate2
   1      | 12 Main Street   | 2019-12-16   | 2019-12-15   | 2019-12-14
   2      | 456 South Street | 2019-12-13   | 2019-12-12   | 2019-12-11

Upvotes: 0

Views: 63

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521289

Assuming you expect a maximum of three records for each address, we can try pivoting with the help of ROW_NUMBER:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY AddressId ORDER BY CreatedDate DESC) rn
    FROM yourTable
)

SELECT
    AddressId,
    Address,
    MAX(CASE WHEN rn = 1 THEN CreatedDate END) AS CreatedDate0,
    MAX(CASE WHEN rn = 2 THEN CreatedDate END) AS CreatedDate1,
    MAX(CASE WHEN rn = 3 THEN CreatedDate END) AS CreatedDate2
FROM cte
GROUP BY
    AddressId,
    Address;

Upvotes: 1

Related Questions