Access functions to SQL Server

I'm upsizing MS-Access to SQL Server 2005.
I need to convert the following functions:
TRANSFORM
PIVOT
FORMAT
MID

Are there any similar functions in SQL Server?

Also I have a query as follows:

SELECT Mid$([AccountNumber],3,8) AS [Account#], Format([checkamount]*100,"000000000") AS Amount, IIf(IsNull([statusdate]),"",Format([statusdate],"yyyymmdd")) AS [Date]
FROM tblResult;

How do I convert this in SQL Server query?

Hi,

SELECT
SUBSTRING([AccountNumber],3,8) AS [Account#],
RIGHT('000000000' + CAST([checkamount]*100 AS VARCHAR(9)),9) AS Amount,
ISNULL(CONVERT([statusdate],112),'') AS [Date]
FROM tblResult

TRANSFORM => Can be done with Pivot in SQL Server 2005. In SQL 2k you will have to use CASE statements to produce crosstabs queries.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Thanks.

There's one error: StatusDate is not a defined system type
StatusDate is defined as Date in database.

also, is it possible to give an example for pivot?

I have a Transform query as follows:

TRANSFORM Sum(CDbl([CheckAmount]))

AS [The Value]

SELECT "Total

Amount of Checks Issued" AS Type, tblResult.AccountNumber,

tblResult.CheckDate, tblResult.Status, Sum(CDbl([CheckAmount]))

AS Total

FROM tblResult

GROUP BY "Total Amount

of Checks Issued", tblResult.AccountNumber, tblResult.CheckDate,

tblResult.Status

PIVOT IIf(IsNull([statusdate]),"Outstanding",Format([StatusDate],"Short

Date"));

|||

Please check out the following resources for migrating from Access to SQL Server. We also have a tool to help with that.

http://www.microsoft.com/sql/solutions/migration/access/default.mspx

|||I'm using SSMA to convert tables, but unfortunately this tool does not convert queries.|||I don't know the exact behavior of TRANSFORM so you will have to ask in the SSMA newsgroup or look at the examples in Books Online and convert your query. You can also post your current schema and expected results & it will be easier to suggest the query in SQL Server.|||Here's the schema and query:
tblResult
[Autonumber] = <int>
,[AccountNumber] = <varchar(50)>
,[CheckNumber] = <float>
,[CheckAmount] = <money>
,[CheckDate] = <datetime>
,[StatusDate] = <datetime>
,[Status] = <varchar(50)>
,[AccountID] = <varchar(50)>
,[Payee] = <varchar(50)>
,[Soc] = <varchar(50)>
,[ID-1] = <varchar(255)>
,[ID-2] = <varchar(255)>
,[ID-3] = <varchar(255)>

Query:
In MS Access:

SELECT Mid$([AccountNumber],3,8) AS [Account#], Format([checkamount]*100,"000000000") AS Amount, IIf(IsNull([statusdate]),"",Format([statusdate],"yyyymmdd")) AS [Date]
FROM tblResult;

SQL Server:
SELECT
SUBSTRING([AccountNumber],3,8) AS [Account#],
RIGHT('000000000' + CAST([checkamount]*100 AS VARCHAR(9)),9) AS Amount,
ISNULL(CONVERT([statusdate],112),'') AS [Date]
FROM tblResult