Sql defining a string the number of times / how it looks like other strings

Question

I want to revise some relatively simple information in the most efficient way: Using a limited selection of sample data:

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()))

And turning it into this:

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

Where, in principle, if the ID has both one and an ordinary gift during the year, then call it “both gifts”, if there is only one gift, then “only gift”, and if there is only an ordinary gift, then “ordinary gift "

This data will then be used as part of another larger request.

It’s very easy for me to handle if the ID ever gave one or the usual (or both) gift, but I try my best to work on it year after year, without creating a series of temporary tables that significantly slow down the work

Thanks in advance:)

EDIT. , : 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

Answer 2

, .

Select * 
From
(
Select  ID, 
        'both gift' as 'PaymentType',
        Datepart(Year, payment_date) as 'Years'
From #data
Group by Id, Datepart(Year, payment_date)
Having Count(Distinct(Payment_Type)) > 1

    Union All

Select  ID, 
        Max(Payment_Type),
        Datepart(Year, payment_date)
From #data
Group by Id, Datepart(Year, payment_date)
Having Count(Distinct(Payment_Type)) = 1

    Union All

Select  ID, 
        Null,
        Datepart(Year, payment_date)
From #data
Group by Id, Datepart(Year, payment_date)
Having Count(Distinct(Payment_Type)) = 0
)q

Pivot  (max(PaymentType) for Years in ([2002],[2003],[2004],[2005],[2006],[2007],[2008],[2009],[2010])
)p

Answer 3

WITH Data AS
    ( SELECT id, YEAR(payment_date) AS payment_year,
        CASE
            WHEN MAX(payent_type)<> MIN(payent_type)
            THEN 'Both gifts'
            ELSE MAX(payent_type)
        END AS payent_type
    FROM #data
    GROUP BY YEAR(payment_date),
        id
    )
select id,[2002],[2003],[2004],[2005],[2006],[2007],[2008],[2009],[2010]
from data
Pivot  (max(payent_type) for 
      payment_year in ([2002],[2003],[2004],[2005],[2006],[2007],[2008],[2009],[2010])) pvt