在 SQL Server 2008 中将行转换为列

发布于 2025-01-10 07:26:25 字数 1304 浏览 1 评论 0原文

我们有一张名为许可证的表。它看起来像这样：

CustNum	LicenseAddress	License	ExpiryDate
155	123	Y32CA	12/31/2018
155	998	Y32CB	12/31/2020
155	568	Y32CC	12/31/2022

这是我想要的样子：

LicAddr1	Lic1	ExpiryDate1	LicAddr2	Lic2	ExpiryDate2	LicAddr3	Lic3	ExpiryDate3
123	Y32CA	12/31/2018	998	Y32CB	12/31/2020	568	Y32CC	12/31/2022

这是我当前的查询，但它只返回 NULL：

SELECT LicAddr1, 
       Lic1,
       ExpiryDate1,
       LicAddr2,
       Lic2,
       ExpiryDate2,
       LicAddr3,
       Lic3,
       ExpiryDate3
FROM (SELECT CustNum, LicenseAddress, License, ExpiryDate FROM Licenses) d
PIVOT (
       MAX(ExpiryDate) 
       FOR CustNum IN (LicAddr1, Lic1, ExpiryDate1, LicAddr2, Lic2, ExpiryDate2, LicAddr3, Lic3, ExpiryDate3) 
      ) piv

我做错了什么？

原文

We have one table called Licenses. This is what it looks like:

CustNum	LicenseAddress	License	ExpiryDate
155	123	Y32CA	12/31/2018
155	998	Y32CB	12/31/2020
155	568	Y32CC	12/31/2022

Here is what I want it to look like:

LicAddr1	Lic1	ExpiryDate1	LicAddr2	Lic2	ExpiryDate2	LicAddr3	Lic3	ExpiryDate3
123	Y32CA	12/31/2018	998	Y32CB	12/31/2020	568	Y32CC	12/31/2022

Here is the query I have currently, however it’s only returning NULLs:

SELECT LicAddr1, 
       Lic1,
       ExpiryDate1,
       LicAddr2,
       Lic2,
       ExpiryDate2,
       LicAddr3,
       Lic3,
       ExpiryDate3
FROM (SELECT CustNum, LicenseAddress, License, ExpiryDate FROM Licenses) d
PIVOT (
       MAX(ExpiryDate) 
       FOR CustNum IN (LicAddr1, Lic1, ExpiryDate1, LicAddr2, Lic2, ExpiryDate2, LicAddr3, Lic3, ExpiryDate3) 
      ) piv

What am I doing wrong?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

人海汹涌 2025-01-17 07:26:25

PIVOT 并不是您真正想要的，特别是因为您试图从每一行获取多个值（这与聚合 PIVOT 配合得不太好）尝试提供）。

我假设您需要最近的三个许可证，在这种情况下，我们可以为每个 CustNum 应用一个行号，按 ExpiryDate 排序（最新的在前），然后翻转它们所以它们是从左到右从最旧到最新：

;WITH cte AS
(
  SELECT CustNum, LicenseAddress, License, ExpiryDate,
    rn = ROW_NUMBER() OVER (PARTITION BY CustNum ORDER BY ExpiryDate DESC)
  FROM dbo.Licenses
)
SELECT CustNum,
  LicAddr1    = MAX(CASE WHEN rn = 3 THEN LicenseAddress END),
  Lic1        = MAX(CASE WHEN rn = 3 THEN License        END),
  ExpiryDate1 = MAX(CASE WHEN rn = 3 THEN ExpiryDate     END),
  LicAddr2    = MAX(CASE WHEN rn = 2 THEN LicenseAddress END),
  Lic2        = MAX(CASE WHEN rn = 2 THEN License        END),
  ExpiryDate2 = MAX(CASE WHEN rn = 2 THEN ExpiryDate     END),
  LicAddr3    = MAX(CASE WHEN rn = 1 THEN LicenseAddress END),
  Lic3        = MAX(CASE WHEN rn = 1 THEN License        END),
  ExpiryDate3 = MAX(CASE WHEN rn = 1 THEN ExpiryDate     END)
FROM cte
GROUP BY CustNum;

结果：

CustNum	LicAddr1	Lic1	ExpiryDate1	LicAddr2	Lic2	ExpiryDate2	LicAddr3	Lic3	到期日期3
155	123	Y32CA	2018-12-31	998	Y32CB	2020-12-31	568	Y32CC	2022-12-31

示例 db<>fiddle

PIVOT isn't really what you're after, especially since you're trying to get multiple values from each row (which doesn't work very well with the aggregation PIVOT tries to offer).

I'm assuming here you want the most recent three licenses, in which case we can apply a row number per CustNum, ordered by ExpiryDate (newest first), then flip them so they are left-to-right oldest-to-newest:

;WITH cte AS
(
  SELECT CustNum, LicenseAddress, License, ExpiryDate,
    rn = ROW_NUMBER() OVER (PARTITION BY CustNum ORDER BY ExpiryDate DESC)
  FROM dbo.Licenses
)
SELECT CustNum,
  LicAddr1    = MAX(CASE WHEN rn = 3 THEN LicenseAddress END),
  Lic1        = MAX(CASE WHEN rn = 3 THEN License        END),
  ExpiryDate1 = MAX(CASE WHEN rn = 3 THEN ExpiryDate     END),
  LicAddr2    = MAX(CASE WHEN rn = 2 THEN LicenseAddress END),
  Lic2        = MAX(CASE WHEN rn = 2 THEN License        END),
  ExpiryDate2 = MAX(CASE WHEN rn = 2 THEN ExpiryDate     END),
  LicAddr3    = MAX(CASE WHEN rn = 1 THEN LicenseAddress END),
  Lic3        = MAX(CASE WHEN rn = 1 THEN License        END),
  ExpiryDate3 = MAX(CASE WHEN rn = 1 THEN ExpiryDate     END)
FROM cte
GROUP BY CustNum;

Results: