sql按次数定义一行/它如何显示为其他行

Question

我希望以最有效的方式对一些相对简单的信息进行重新分类:使用有限的样本数据:

CREATE TABLE #data
(id varchar(30)
,payent_type varchar(30)
,payment_date DATETIME)

INSERT INTO #data values ('001','single gift',DATEADD(MM,-12,GETDATE()))
INSERT INTO #data values ('001','regular gift',DATEADD(MM,-39,GETDATE()))
INSERT INTO #data values ('002','regular gift',DATEADD(MM,-06,GETDATE()))
INSERT INTO #data values ('003','single gift',DATEADD(MM,-96,GETDATE()))
INSERT INTO #data values ('003','regular gift',DATEADD(MM,-96,GETDATE()))
INSERT INTO #data values ('003','single gift',DATEADD(MM,-1,GETDATE()))
INSERT INTO #data values ('004','single gift',DATEADD(MM,-54,GETDATE()))
INSERT INTO #data values ('005','regular gift',DATEADD(MM,-2,GETDATE()))
INSERT INTO #data values ('005','regular gift',DATEADD(MM,-8,GETDATE()))
INSERT INTO #data values ('006','single gift',DATEADD(MM,-12,GETDATE()))
INSERT INTO #data values ('007','regular gift',DATEADD(MM,-2,GETDATE()))
INSERT INTO #data values ('007','regular gift',DATEADD(MM,-6,GETDATE()))
INSERT INTO #data values ('008','single gift',DATEADD(MM,-1,GETDATE()))
INSERT INTO #data values ('009','single gift',DATEADD(MM,-80,GETDATE()))
INSERT INTO #data values ('010','single gift',DATEADD(MM,-54,GETDATE()))

然后把它变成这样:

ID   |   2005  |  2006  |  2007         |  2008  |  2009       |  2010
001  |   NULL  |  NULL  |  regular gift |  NULL  |  Both gifts |  NULL

基本上，如果 ID 在一年内既有一份礼物又有一份普通礼物，则称其为“两份礼物”，如果只有一份礼物，则称其为“单份礼物”，如果只有一份普通礼物，则称其为“普通礼物” '.

此数据将用作另一个更大查询的一部分。

我发现很容易计算出一个 ID 是否曾经赠送过一份或定期(或两者)礼物，但我正在努力逐年计算而不创建一系列临时表会大大降低速度

提前致谢:)

编辑这是我拥有的实际数据的一个非常简化的版本 - 真实数据每个 ID 最多 200 行，并且每年可以包含每种类型的多个礼物。

Answer 1

这符合您的需求吗？

;with IDYearSummary as
(
    select
        id,
        MAX(CASE payment_type WHEN 'single gift' THEN 1 ELSE 0 END) as single,
        MAX(CASE payment_type WHEN 'regular gift' THEN 1 ELSE 0 END) as regular,
        DATEPART(year,payment_date) as year
    from
        #data
    group by
        id,DATEPART(year,payment_date)
), MixGifts as
(
    select
        id,
        CASE
            WHEN single=1 and regular=1 THEN 'both'
            WHEN single=1 THEN 'single'
            WHEN regular=1 THEN 'regular'
        END as gifts,
        year
    from
        IDYearSummary
)
select
    id,
    [2002],
    [2003],
    [2004],
    [2005],
    [2006],
    [2007],
    [2008],
    [2009],
    [2010]
from
    MixGifts
        pivot (MAX(gifts) FOR year in ([2002],[2003],[2004],[2005],[2006],[2007],[2008],[2009],[2010])) as pvt


drop table #data