sql defining a row by number of times / how it appears as other rows

Question

I'm looking to recategorise some relatively simple information in the most efficient way possible: 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 basically if ID has both a single and a regular gift for a year then call it 'both gifts', if there is only a single gift then 'single gift' and if there is only a regular gift then 'regular gift'.

This data would then be used as part of another larger query.

I'm finding it easy enough to work out if an ID ever gave a single or regular (or both) gift, but I'm struggling to work it out on a year-by-year basis without creating a series of temporary tables which slows things down massively

Thanks in advance :)

EDIT This is a very simplified version of the actual data I have - the real data has up to 200 rows per ID, and can contain multiple gifts per year of each type.

Answer 1

This query works using the test data you have provided.

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 2

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

Answer 3

Does this match what you need?

;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