How to Translate Access Crosstab Query to T-SQL

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

aushknotes - 30 Jun 2008 06:24 GMT

I have just built a crosstab query on Access & I am having trouble
translating it on to SQL Server 2000 or 2005. Browse thru the online help in
T-SQL Reference but no much help. Would you guys give a helping hand please?

MS-Access (2003)
TRANSFORM Count(DM.DMID) AS CountDMID
SELECT C29.C29DESCRIPTION AS PG
FROM C29 INNER JOIN (C4 INNER JOIN DM ON C4.C4ID = DM.C4ID) ON C29.C29ID =
DM.C29ID
WHERE (((DM.C8ALIAS)="XYZ"))
GROUP BY C29.C29DESCRIPTION, DM.C29DESCRIPTION
ORDER BY C29.C29DESCRIPTION, C4.C4DESCRIPTION
PIVOT C4.C4DESCRIPTION;

Many thanks in advance.

Reply to this Message

Plamen Ratchev - 30 Jun 2008 14:06 GMT

On SQL Server 2005 take a look at the PIVOT operator:
http://msdn.microsoft.com/en-us/library/ms177410.aspx

Have in mind PIVOT performs static pivoting (you have to know the pivot
values in advance). For dynamic pivoting (which is more like TRANSFORM) take
a look at the following articles:
http://www.sqlteam.com/article/dynamic-cross-tabs-pivot-tables
http://www.sqlmag.com/Articles/ArticleID/93907/93907.html
http://weblogs.sqlteam.com/jeffs/archive/2005/05/02/4842.aspx

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Reply to this Message

aushknotes - 30 Jun 2008 23:19 GMT

Hi Plamen,

Thanks very much for your reading suggestions and the limitations on static
vs dynamic pivot tables. I guess I will probably consider executing the
Transform Crosstab query on Access DB, output the query results to a
spreadsheet or webpage then.

Many thanks!

> On SQL Server 2005 take a look at the PIVOT operator:
> http://msdn.microsoft.com/en-us/library/ms177410.aspx
[quoted text clipped - 10 lines]
> Plamen Ratchev
> http://www.SQLStudio.com

Reply to this Message