[help]converting access sql to SQL Server 2005

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

[help]converting access sql to SQL Server 2005

Author

Topic

MIGHTY.JOE
Starting Member

6 Posts

Posted - 2010-09-09 : 04:27:32

Hi..
I have sample SQL code in Access, look like this :


TRANSFORM First([FirstName] & " " & [LastName] & " - " & [Status]) AS Name
SELECT qry_DateTemp.Date
FROM Customers RIGHT JOIN qry_DateTemp ON Customers.CustomerID = qry_DateTemp.CustomerID
GROUP BY qry_DateTemp.Date
PIVOT qry_DateTemp.RoomNumber In (201,202,203,204,205,206,207,208,209,210);

Please somebody help me to convert that code to T-SQL (SQL Server 2005). Thank you for assistance..

Sachin.Nand

2937 Posts

Posted - 2010-09-09 : 04:53:46

Can you please tell what does TRANSFORM function of Access do?

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

MIGHTY.JOE
Starting Member

6 Posts

Posted - 2010-09-09 : 07:09:59

quote:
Originally posted by Idera

Can you please tell what does TRANSFORM function of Access do?

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

I can not explain by words what exactly functon of TRANSFORM in Access
but you may understand if i show the result like :

Sachin.Nand

2937 Posts

Posted - 2010-09-09 : 07:34:59

Is that the final reult of your query or just the result of TRANSFORM?

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2010-09-09 : 08:06:27

sample output and data would make it easier. I'm not familiar with access.
think this is what you are looking for,,

SELECT 
qry_DateTemp.Date,
max(case when qry_DateTemp.RoomNumber=201 then (FirstName+' '+LastName+' '+Status) end) as [201],
max(case when qry_DateTemp.RoomNumber=202 then (FirstName+' '+LastName+' '+Status) end) as [202],
max(case when qry_DateTemp.RoomNumber=203 then (FirstName+' '+LastName+' '+Status) end) as [203],
max(case when qry_DateTemp.RoomNumber=204 then (FirstName+' '+LastName+' '+Status) end) as [204],
..
..
FROM Customers RIGHT JOIN qry_DateTemp ON Customers.CustomerID = qry_DateTemp.CustomerID
where qry_DateTemp.RoomNumber In (201,202,203,204,205,206,207,208,209,210)
GROUP BY qry_DateTemp.Date

You could re-write it using pivot operator in sql 2005 and above.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2010-09-09 : 08:11:35

Also, Why would you pivot it like such ? Is it just a reporting need ?

MIGHTY.JOE
Starting Member

6 Posts

Posted - 2010-09-09 : 23:31:11

quote:
Originally posted by Idera

Is that the final reult of your query or just the result of TRANSFORM?

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Thanks for the reply, Idera..
the image is the final result of this query :


TRANSFORM First([FirstName] & " " & [LastName] & " - " & [Status]) AS Name
SELECT qry_DateTemp.Date
FROM Customers RIGHT JOIN qry_DateTemp ON Customers.CustomerID = qry_DateTemp.CustomerID
GROUP BY qry_DateTemp.Date
PIVOT qry_DateTemp.RoomNumber In (201,202,203,204,205,206,207,208,209,210);

not the TRANSFORM

MIGHTY.JOE
Starting Member

6 Posts

Posted - 2010-09-09 : 23:38:52

quote:
Originally posted by sakets_2000

Also, Why would you pivot it like such ? Is it just a reporting need ?

Yes Sir..
My goal is to determine the status of rooms at certain dates.
whether these rooms were vacant or occupied

I'm not familiar with pivot but I've try to use it in my query.
unfortunately I get stuck..

Thanks for your attention..

Sachin.Nand

2937 Posts

Posted - 2010-09-10 : 02:12:48

I think this will get the o/p which you want.


SELECT * FROM
(
SELECT qry_DateTemp.Date
FROM Customers RIGHT JOIN qry_DateTemp ON Customers.CustomerID = qry_DateTemp.CustomerID
GROUP BY qry_DateTemp.Date
)u
PIVOT (min([FirstName] & " " & [LastName] & " - " & [Status]) for qry_DateTemp.RoomNumber In (201,202,203,204,205,206,207,208,209,210))v;

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

MIGHTY.JOE
Starting Member

6 Posts

Posted - 2010-09-12 : 22:39:23

quote:

Originally posted by sakets_2000

sample output and data would make it easier. I'm not familiar with access.
think this is what you are looking for,,

SELECT 
qry_DateTemp.Date,
max(case when qry_DateTemp.RoomNumber=201 then (FirstName+' '+LastName+' '+Status) end) as [201],
max(case when qry_DateTemp.RoomNumber=202 then (FirstName+' '+LastName+' '+Status) end) as [202],
max(case when qry_DateTemp.RoomNumber=203 then (FirstName+' '+LastName+' '+Status) end) as [203],
max(case when qry_DateTemp.RoomNumber=204 then (FirstName+' '+LastName+' '+Status) end) as [204],
..
..
FROM Customers RIGHT JOIN qry_DateTemp ON Customers.CustomerID = qry_DateTemp.CustomerID
where qry_DateTemp.RoomNumber In (201,202,203,204,205,206,207,208,209,210)
GROUP BY qry_DateTemp.Date

You could re-write it using pivot operator in sql 2005 and above.

Thx Sir, It's Works... :)

MIGHTY.JOE
Starting Member

6 Posts

Posted - 2010-09-12 : 22:39:59

quote:
Originally posted by Idera

I think this will get the o/p which you want.

SELECT * FROM
(
SELECT qry_DateTemp.Date
FROM Customers RIGHT JOIN qry_DateTemp ON Customers.CustomerID = qry_DateTemp.CustomerID
GROUP BY qry_DateTemp.Date
)u
PIVOT (min([FirstName] & " " & [LastName] & " - " & [Status]) for qry_DateTemp.RoomNumber In (201,202,203,204,205,206,207,208,209,210))v;

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Thx Sir, The Problem has been solved !! :)

niravlvyas
Starting Member

1 Post

Posted - 2010-10-26 : 08:45:36

HI Joe,
I too have a same situation, I am not use to PIVOT , Transform in SQL.Here is my query in Access

TRANSFORM Sum(ProductSupply.Alloc1) AS [Rank 1]
SELECT Supplier.SupplierName AS Supplier FROM ProductSupply INNER JOIN Supplier ON ProductSupply.SupplierID = Supplier.ID
WHERE (((ProductSupply.TenderDate)>=#01/01/2009# And (ProductSupply.TenderDate)<=#12/31/2009#)) GROUP BY Supplier.SupplierName
PIVOT Format(ProductSupply.TenderDate,"mm-dd-yy");

Now I need it to convert in SQL..

Any help is apprecaited

Thanks And Regards
Nirav Vyas

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources