How to Convert Access Transform/Pivot Query for SQL Server

[Charles Law]

I have two queries that I use in Access:

<qrySelect>
SELECT table2.column1, table2.column2, table2.column3, table1.column4 FROM
table1, table2 WHERE table1.column1 = table2.column1
</qrySelect>

<qryResult>
TRANSFORM First(qrySelect.column3) AS FirstOfCode
SELECT qrySelect.column1, qrySelect.column4, First(qrySelect.column3) AS
[Total Of Code] FROM qrySelect GROUP BY qrySelect.column1, qrySelect.column4
PIVOT qrySelect.column2
</qryResult>

I now wish to use them against a SQL Server database but I am unfamiliar
with the equivalent construct of Transform/Pivot in SQL Server. I read in
the MS documentation that I should use ROLLUP on the GROUP BY clause, but
try as I might I cannot make it work.

Can any one suggest how this might be done?

If there is a better newsgroup to post this question in I would be grateful
if someone could point me in the right direction.

TIA

Charles Law

 

[Jack D. Ripper]

Check out the RAC utility for S2k.It's similar
to Access crosstab but much more powerful with
many features/options.

www.rac4sql.net

 

[Charles Law]

Thanks Jack. I was really hoping for a straight conversion, without the need
for third-party tools.

From what I have read, ROLLUP should be the S2K way of doing TRANSFORM ...
PIVOT, but I am unable to find any examples of how one can represent the
other.

If you have any pointers to samples of the syntax I would be very grateful.

Charles

 

[David Chavez]

Charles,
Try this link:
http://support.microsoft.com/default.aspx?scid=KB;EN-
US;q175574&

I had the same problem too and this link helped me.

-----Original Message-----
I have two queries that I use in Access:

<qrySelect>
SELECT table2.column1, table2.column2, table2.column3, table1.column4 FROM
table1, table2 WHERE table1.column1 = table2.column1
</qrySelect>

<qryResult>
TRANSFORM First(qrySelect.column3) AS FirstOfCode
SELECT qrySelect.column1, qrySelect.column4, First (qrySelect.column3) AS
[Total Of Code] FROM qrySelect GROUP BY

qrySelect.column1, qrySelect.column4