Help please for total Newbie to MS SQL re: Transform

Question

Post reply

Help please for total Newbie to MS SQL re: Transform

richosr

Newbie

Points: 3
More actions
June 5, 2012 at 11:44 pm

#392445

I am currently trying to upgrade some of my ASP apps from Access databases to MS SQL most are working fine in both Access and MS SQL.
I have a Transform query that accesses an MS Access MDB in an ASP page that works great, and I want to get it to work on MS SQL server, All my other queries work with no alteration but I am having a problem with this one.
It uses a lot of variables to fix the column headings.
If anyone has any ideas they would be gratefully received.
The date table is just a list of every date for the next 5 years.
The bookings table contains data like:
IDrbNumberrbTyperbBookdaterbBookTyperbBookRef
4037Standard Double02 June 2012B447361938
4027Standard Double01 June 2012B447361938
4017Standard Double31 May 2012B447361938
4007Standard Double30 May 2012B447361938
3997Standard Double29 May 2012B447361938
3987Standard Double28 May 2012B447361938
3977Standard Double27 May 2012B447361938
And the results from the query look like:
Here is my current query:
strsql="TRANSFORM First(tblBookings.rbBookType) AS FirstOfrbBookType SELECT tblBookings.rbNumber, tblBookings.rbType FROM tblBookings RIGHT JOIN tblDates ON tblBookings.rbBookdate = tblDates.dDates " _
& "WHERE (((tblDates.dDates)>=#" & formatDateTime(dtStart,1) & "# And (tblDates.dDates)<#" & formatDateTime(dtEnd,1) & "#) AND ((tblBookings.rbBookType)<>'C')) OR (((tblDates.dDates)>=#" & formatDateTime(dtStart,1) & "# And (tblDates.dDates)<#" & formatDateTime(dtEnd,1) & "#) AND ((tblBookings.rbNumber) Is Not Null) AND ((tblBookings.rbBookType) Is Null)) " _
& "GROUP BY tblBookings.rbNumber, tblBookings.rbType ORDER BY tblBookings.rbNumber PIVOT tblDates.dDates In ('" & CStr(formatDateTime(dtStart,1)) & "','" & formatDateTime(dtStart + 1,1) & "','" & formatDateTime(dtStart + 2,1) & "','" & formatDateTime(dtStart + 3,1) & "','" & formatDateTime(dtStart + 4,1) & "','" & formatDateTime(dtStart + 5,1) & "','" & formatDateTime(dtStart + 6,1) & "','" & formatDateTime(dtStart + 7,1) & "','" & formatDateTime(dtStart + 8,1) & "','" & formatDateTime(dtStart + 9,1) & "','" & formatDateTime(dtStart + 10,1) & "','" & formatDateTime(dtStart + 11,1) & "','" & formatDateTime(dtStart + 12,1) & "','" & formatDateTime(dtStart + 13,1) & "');"
Any help, suggestions gratefully received.
kind regards
Steve

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

vinu512 SSCoach Points: 15729 More actions · Answer 1

Sorry, one of us is missing out on something. What is the problem???....How do you want your resultset to look like?

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

richosr Newbie Points: 3 More actions · Answer 2

The results shown are from Access Database, but this query will not work with MS SQL Server. I need to get the same result with MS SQL as I could with the TRANSFORM query in Access.

Sorry if I didn't make myself clear.

regards

Steve

vinu512 SSCoach Points: 15729 More actions · Answer 3

You can use a Dynamic Pivot as follows:

--Creating Tables

Create Table Ex

(IDint,

rbNumber int,

rbTypevarchar(20),

rbBookdatevarchar(20),

rbBookTypeChar(1),

rbBookRef varchar(20) )

--Inserting Sample Data

Insert Into Ex

Select 403,7,'Standard Double','02 June 2012','B','447361938'

Union ALL

Select 402,7,'Standard Double','01 June 2012','B','447361938'

Union ALL

Select 401,7,'Standard Double','31 May 2012','B','447361938'

Union ALL

Select 400,7,'Standard Double','30 May 2012','B','447361938'

Union ALL

Select 399,7,'Standard Double','29 May 2012','B','447361938'

Union ALL

Select 398,7,'Standard Double','28 May 2012','B','447361938'

Union ALL

Select 397,7,'Standard Double','27 May 2012','B','447361938'

--Dynamic Pivot

Declare @cols varchar(max), @sql varchar(max)

Declare @temp Table(Cols Varchar(max))

Insert into @temp

Select Distinct rbBookDate From Ex

Select @cols = Coalesce(@cols + ', ', '') + QUOTENAME(Cols) From @temp

Set @sql = 'Select Id, '+@cols+'From Ex

Pivot

(max(rbBookType) For rbBookDate IN ('+@cols+')) As pvt'

Execute (@sql)

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

richosr Newbie Points: 3 More actions · Answer 4

Thank you for that, I will give it a try.

kind regards

Steve

vinu512 SSCoach Points: 15729 More actions · Answer 5

you're Welcome.:-)

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉