--Creating Tables 

Create Table Store_Audit_Type 
	(
		Audit_Type_Id Int, 
		Audit_Type_Description NVarchar(30) 
	) 

Create Table Store_Audit_Details 
	(
		StoreId Int, 
		Audit_Type_Id Int, 
		CreatedDate Date 
	) 
	

--Inserting Sample Data 
		
Insert Into Store_Audit_Type 
Select 1, 'New' 
Union ALL
Select 2, 'Closed' 
Union ALL
Select 3, 'ReOpen'
	
Insert Into Store_Audit_Details 
Select 1, 1, '2012/01/01' 
Union ALL 
Select 1, 2, '2012/04/01'
Union ALL 
Select 1, 3, '2012/06/01'
Union ALL 
Select 1, 2, '2012/08/01'
Union ALL 
Select 1, 3, '2012/10/01'
Union ALL 
Select 1, 2, '2012/11/01'
Union ALL 
Select 2, 1, '2012/01/05'
Union ALL 
Select 2, 2, '2012/05/10'
Union ALL 
Select 3, 1, '2012/12/01' 


--Dynamic Pivot 

Declare @sql NVarchar(MAX), @cols NVarchar(MAX) 
Select @cols = STUFF((Select DISTINCT ',MAX(Case When rn = ''' + rn + ''' Then CreatedDate Else NULL End) As ' + rn 
						From (Select DISTINCT Audit_Type_Description + Cast(ROW_NUMBER() Over (Partition By StoreId, Audit_Type_Description Order By StoreId) As Varchar) As rn
								From Store_Audit_Details As a 
								JOIN Store_Audit_Type As b ON a.Audit_Type_Id = b.Audit_Type_Id ) As q FOR XML PATH('')),1,1,'') 
Set @sql = 'Select StoreId, ' + @cols  
		   + ' From 
					(
						Select StoreId, Audit_Type_Description, CreatedDate, Audit_Type_Description + Cast(ROW_NUMBER() Over (Partition By StoreId, Audit_Type_Description Order By StoreId) As Varchar) As rn
						From Store_Audit_Details As a 
						JOIN Store_Audit_Type As b ON a.Audit_Type_Id = b.Audit_Type_Id 
					) As p 
				Group By StoreId' 
Execute (@sql)