SQL Server展平某些列
SQL Server展平某些列
提问于 2019-12-17 11:21:17
如何展平SQL Server查询中的特定列？
数据来源：
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预期结果：
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回答 1
Stack Overflow用户
发布于 2019-12-17 11:24:55
假设您期望每个地址最多有三条记录，我们可以尝试在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;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/59367179
复制相关文章
相似问题
腾讯云开发者
