Solved

TRANSFORM Access query in SQL Server?

Posted on 2011-02-14
5
319 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

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.

808 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