Solved

TRANSFORM Access query in SQL Server?

Posted on 2011-02-14
5
304 Views
Last Modified: 2012-05-11
I have this old MS Access query that I need to rewrite for SQL Server.

TRANSFORM Last(MyTable.Desc) AS LastOfDesc SELECT MyTable.Name FROM MyTable WHERE (((MyTable.Name)<>'') AND (Datediff(day,[Date],'2011-01-01')>=lTimeFrame * -1) And (Datediff(day,[Date],'2011-01-01') <=lTimeFrame) GROUP BY MyTable.Name ORDER BY MyTable.Name PIVOT Year([Date]) & chr(13) & Month([Date]) & chr(13) & Day([Date]));

Any ideas?
0
Comment
Question by:koossa
  • 2
  • 2
5 Comments
 
LVL 5

Accepted Solution

by:
spikelly earned 167 total points
ID: 34887040
ACCESS queries are full compatible with SQL Server ; some differences are on the datatypes and some syntax which need to be ajusted.

Regarding your query, the select statement is the same, the change to make is on the PIVOT syntax. If you want to pivot your result, there is a PIVOT command in SQL to help you achieve this. Use the help to learn more, it's well explained !

Cheers...
0
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 333 total points
ID: 34887180
whilst transform has an equivalence to the MS SQL Server PIVOT clause

there is no direct equivalence for the FIRST/LAST directives in SQL Server...

could you explain what the statement is trying to achieve?
0
 

Author Comment

by:koossa
ID: 34887233
This is just to pickup anomalies if there happened to be a lot of the same descriptions in a time frame.
So I think I could use anything else in the place of Last(
0
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 333 total points
ID: 34887349
something like this?
select name,[-5],[-4],[-3],[-2],[-1],[0],[1],[2],[3],[4],[5]
from (select name,datediff(d,[date],[20110101]) as Dn
        from mytable 
     ) as x
pivot (max(desc) for dn in ([-5],[-4],[-3],[-2],[-1],[0],[1],[2],[3],[4],[5])) as pvt
 where x.dn >= @ltimeframe * -1 and x.dn <= @ltimeframe
 order by 1

Open in new window

0
 

Author Closing Comment

by:koossa
ID: 34887404
Thank you
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now