Pivot with ID and Dates

Question

I have this data set and Im trying to create a pivot table for the data below. I having some issues when trying to put in the date for each type across.

 Member ID  Type     Date
   1         A     12/5/2014
   1         b     3/6/2014
   2         a     6/9/2015
   2         b     3/2/2015
   2         c     6/1/2014
   3         a     6/5/2014
   3         c     7/6/2014
   4         c     9/13/2014
   5         a     7/25/2014
   5         b     6/24/2014
   5         c     2/24/2014

Then

I would like it to come out as a pivot table, like this:

   Member ID    A       A date       B        B date      c     C date
   1           Yes      12/5/2014   yes      3/6/2014    Null    Null
   2           Yes      6/9/2015    yes      3/2/2015    Yes    6/1/2014
   3           Yes      6/5/2014    Null      Null       Yes    7/6/2014
   4           Null       Null      Null      Null       Yes    9/13/2014
   5           Yes      7/25/2014   yes      6/24/2014   Yes    2/24/2014

i got this far not including the dates

     SELECT
      MemberID 
     ,Type
     --,Date

     into #Test9

      FROM [Data].[dbo].[Test]

I create the Temp table then try to pivot without date

     Select *
      From #Test9
        Pivot ( count(type)
         for type in (a]
          ,[b]
          ,[c])) as pvt

Would someone please help.

score 1 · Accepted Answer · edited May 23 '17 at 11:43

Assuming each member will only have one value for each type you could use conditional aggregation to get the result you want:

select 
    memberid,
    max(case when type='a' then 'Yes' end) as "a",
    max(case when type='a' then date end) as "a date",
    max(case when type='b' then 'Yes' end) as "b",
    max(case when type='b' then date end) as "b date",
    max(case when type='c' then 'Yes' end) as "c",
    max(case when type='c' then date end) as "c date"
from your_table
group by MemberID

Sample SQL Fiddle

This can quite easily be turned into a dynamic query if you don't have a fixed number of types; there are plenty of good answers here on Stack Overflow that demonstrates how, including the canonical SQL Server Pivot question that I usually link to.

score 1 · Answer 2 · answered Jun 10 '15 at 07:09

try

declare @t table(memberid int,[type] char(1),[date] date)
insert into @t(MemberID,[Type],[Date]) values
  ( 1,'A','12/5/2014'),
   (1,'b','3/6/2014'),
   (2,'a','6/9/2015'),
(2,'b','3/2/2015'),
(2,'c','6/1/2014'),
(3,'a','6/5/2014'),
(3,'c','7/6/2014'),
(4,'c','9/13/2014'),
(5,'a','7/25/2014'),
(5,'b','6/24/2014'),
(5,'c','2/24/2014')

select memberid, case when len(a)>0 then 'Yes' end as [A date],a,
case when len(b)>0 then 'Yes' end as [B date],b,
case when len(c)>0 then 'Yes' end [c date] ,c from @t t
pivot( max([date]) for [type] in (a,b,c)) p

Thanks, i would love to try but i don't have access to do declare or insert @abdulnazar — Amaury, Jun 10 '15 at 12:59

Pivot with ID and Dates

2 Answers2