Hi

I am currently migrating a C# application from using an MS Access backend to an SQL database. I have run into  a bit of problem as of one of my queries is a TRANSFORM / PIVOT with the columns being an unknown number. Does anybody know how to convert the following into the SQL Server equivalent with the PIVOT being dynamic. I have looked at several examples but they all seem to suggest that a stored procedure is required.

TRANSFORM Count(EnquiryActionHistory.RecordGuid) AS CountOfRecordGuid
SELECT (CreatedBy) AS Officer, Count(EnquiryActionHistory.RecordGuid) AS Total
FROM SystemUser INNER JOIN EnquiryActionHistory ON SystemUser.FullNameAndPI = EnquiryActionHistory.UpdatedBy
WHERE ManagersPINumber = '1111111' AND ((DateActionCreated&TimeActionCreated) Between '201408201406' AND '201508201406')
GROUP BY EnquiryActionHistory.CreatedBy, SystemUser.ManagersPINumber
PIVOT ActionType

Thanks in Advance

Pavros

Try

declare @cols nvarchar(max), @sql nvarchar(max);

select @cols = stuff((select distinct ', ' + quotename(A.ActionType)
FROM SystemUser U INNER JOIN EnquiryActionHistory A ON U.FullNameAndPI = A.UpdatedBy 
WHERE U.ManagersPINumber = '1111111' AND 
A.[DateActionCreated&TimeActionCreated] Between '201408201406' AND '201508201406' ORDER BY A.ActionType
FOR XML PATH(''), TYPE).value('.', 'nvarchar('max')),1,2,'')

print @cols -- to test

set @SQL = 'SELECT * FROM (SELECT A.CreatedBy AS Officer, Count(A.RecordGuid) AS Total 
FROM SystemUser U INNER JOIN EnquiryActionHistory A ON U.FullNameAndPI = A.UpdatedBy 
WHERE U.ManagersPINumber = '1111111' AND A.[DateActionCreated&TimeActionCreated] Between '201408201406' AND '201508201406' 
GROUP BY A.CreatedBy, U.ManagersPINumber) src 
PIVOT ( FOR count(Total) IN (' + @cols + ')) pvt';

print @sql -- to test

execute(@sql);

From the top of my head, may need some tweaking.

Posting sample data and expected output would make thing easier and clear. Not every one knows how the Access statemtnt processes in a SQL Server forum.