Is this what you are looking for?
--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)