对一列进行透视，并显示来自另一列的关联值

Question

我有一个table的数据如下:

Fund  |  EffectiveDate | SomeOtherColumns | COST
F123  |  2022-04-25    |  something       | 345
F123  |  2022-04-24    |   fdsdfdff       | 340
F123  |  2022-04-20    |   hi             | 360
F123  |  2022-04-17    |   hello          | 810
F456  |  2022-04-28    |  some other fund | 110
F456  |  2022-04-26    |  some other fund | 220
F456  |  2022-04-25    |  some other fund | 460
F456  |  2022-04-15    |  some other fund | 215

示例定义如下:

CREATE TABLE [dbo].[MyTable](
[Fund] [NCHAR](10) NOT NULL,
[EffectiveDate] [DATE] NOT NULL,
[SomeOtherColumns] [NVARCHAR](50) NULL,
[Cost] [INT] NOT NULL
) ON [PRIMARY]
GO

对于结果查询中的每个基金(本例中为F123和F456)，我希望每个基金每天只有一行。我想取前三名每个基金最新的EffectiveDate，将其与上的COST值一起枢轴的一天。例如，结果将是:

Fund  |  EffectiveDate | SomeOtherColumns | COST | COST DAY BEFORE | COST DAY BEFORE THAT |
F123  |  2022-04-25    | something        | 345  | 340             | 360                  |
F456  |  2022-04-28    | some other fund  | 110  | 220             | 460                  |

我知道我应该用一个PIVOT，但我甚至不知道如何解决它。

我的查询甚至不能编译!

SELECT * FROM (
SELECT TOP(3)
FUND, EffectiveDate, Cost
FROM MyTable
ORDER BY EffectiveDate DESC
) Results
PIVOT (
SUM(Cost)
FOR EffectiveDate
IN (
FUND, EffectiveDate, Cost
)
) AS PivotTable

Answer 1

我不认为这是一个关键的情况，而是一个排名。

您希望每个基金只有一行，其中包括前一个日期的成本，以及在此之前的最后两个日期的成本，对吗?

如果是这样，您可以使用CTE按基金对数据分区进行排名，并为成本生成额外的列，然后对其进行过滤，只显示排名中的第一个值。

:

CREATE TABLE [dbo].[MyTable](
[Fund] [NCHAR](10) NOT NULL,
[EffectiveDate] [DATE] NOT NULL,
[SomeOtherColumns] [NVARCHAR](50) NULL,
[Cost] [INT] NOT NULL
) ON [PRIMARY]
insert into MyTable(Fund  ,  EffectiveDate , SomeOtherColumns , COST) values
('F123'  ,  '2022-04-25'    ,  'something'       , 345),
('F123'  ,  '2022-04-24'    ,  'fdsdfdff'        , 340),
('F123'  ,  '2022-04-20'    ,  'hi'              , 360),
('F123'  ,  '2022-04-17'    ,  'hello'           , 810),
('F456'  ,  '2022-04-28'    ,  'some other fund' , 110),
('F456'  ,  '2022-04-26'    ,  'some other fund' , 220),
('F456'  ,  '2022-04-25'    ,  'some other fund' , 460),
('F456'  ,  '2022-04-15'    ,  'some other fund' , 215)

;with rankingCte as (
select *,
LAG(Cost) over(order by EffectiveDate ASC) [COST DAY BEFORE],
LAG(Cost,2) over(order by EffectiveDate ASC) [COST DAY BEFORE THAT],
rank() over(partition by Fund order by EffectiveDate desc) as dateRanking
from MyTable
)
select Fund, EffectiveDate, SomeOtherColumns, Cost, [COST DAY BEFORE],
[COST DAY BEFORE THAT]
from rankingCte
where dateRanking=1

成本成本前一天成本前一天成本前一天tbody> <<tr>

基金	有效日期	SomeOtherColumns	成本
F123	2022-04-25	是	345	340	360
F456	2022-04-28	其他基金	110	220	460