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.
Assuming each member will only have one value for each type you could use conditional aggregation to get the result you want:
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.