I have a similar table to this dummy data, where there are 3 records for one individual. I would like to change that to one record with multiple columns. What is complicating it for me the most is I want the 3 most current Products based off of Date_Purchased
NameFirst NameLast MbrKey Product DatePurchased John Doe 123456 ProductA 1/1/2015 John Doe 123456 ProductA 2/1/2015 John Doe 123456 ProductB 3/1/2015 John Doe 123456 ProductB 12/1/2015 Joe Smith 987654 ProductA 3/1/2015 Jane Jones 555555 ProductA 1/1/2015 Jane Jones 555555 ProductB 1/1/2015
This is what I have so far:
select MbrKey, NameLast, NameFirst, Case when rn = 1 then Product else null end as Product1, case when rn = 2 then Product else null end as Product2, case when rn = 3 then Product else null end as Product3 from (select t2.* from( select t.*, ROW_NUMBER () over (partition by t.MbrKey order by t.MbrKey, t.DatePurchased desc) as RN from testing t) as t2 where t2.RN between 1 and 3) as t3
I think this got me closer as the results are as follows:
NameFirst NameLast MbrKey Product1 Product2 Product3 Doe John 123456 ProductB NULL NULL Doe John 123456 NULL ProductA NULL Doe John 123456 NULL NULL ProductA Jones Jane 555555 ProductA NULL NULL Jones Jane 555555 NULL ProductB NULL Smith Joe 987654 ProductA NULL NULL
Future State: Below is what I am hoping for.
NameFirst NameLast MbrKey Product1 Product2 Product3 Doe John 123456 ProductB ProductB ProductA Jones Jane 555555 ProductA ProductB Null Smith Joe 987654 ProductA Null Null
Any help would be greatly appreciated!