question

Ernie avatar image
Ernie asked

Access SQL crosstab to SQL pivot

Hi All - we are getting rid of our Access DB which connects to several SQL tables. The reports using Access look great, so I'm not sure this could be re-created in SQL. Here is the Access query that I'd like to convert to SQL: TRANSFORM Count(tbl_History.Date) AS [The Value] SELECT tbl_Sites.Site, tbl_Dept.[Dept/Specialty], tbl_Language.Language, Count(tbl_History.Date) AS [Yearly Total] FROM ((tbl_History LEFT JOIN tbl_Sites ON tbl_History.[Site Code] = tbl_Sites.[Site Code]) LEFT JOIN tbl_Dept ON tbl_History.[Dept Code] = tbl_Dept.[Dept Code]) LEFT JOIN tbl_Language ON tbl_History.[Lang Code] = tbl_Language.[Lang Code] WHERE (((DatePart("yyyy",[Date])) Like [Forms]![frm_Reports]![Report Year]) AND ((tbl_History.[Data from Mainframe])=1)) GROUP BY tbl_Sites.Site, tbl_Dept.[Dept/Specialty], tbl_Language.Language, tbl_History.[Data from Mainframe] ORDER BY tbl_Sites.Site, tbl_Dept.[Dept/Specialty], tbl_Language.Language PIVOT Switch(DatePart("m",[date])=1,"Jan",DatePart("m",[date])=1,"Jan",DatePart("m",[date])=1,"Jan",DatePart("m",[date])=2,"Feb",DatePart("m",[date])=3,"Mar",DatePart("m",[date])=4,"Apr",DatePart("m",[date])=5,"May",DatePart("m",[date])=6,"Jun",DatePart("m",[date])=7,"Jul",DatePart("m",[date])=8,"Aug",DatePart("m",[date])=9,"Sep",DatePart("m",[date])=10,"Oct",DatePart("m",[date])=11,"Nov",DatePart("m",[date])=12,"Dec") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"); This is what the report looks like: ![alt text][1] I would hardcode the (DatePart("yyy",[Date])) to 2013, or get it from tbl_history. Thank you for giving me clues on how to convert! Ernie [1]: /storage/temp/1060-\report.jpg
pivotaccessconvert
\report.jpg (86.8 KiB)
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

The site works on voting. For all helpful answers please indicate this by clicking on the thumbs up next to those answers. If any one answer lead to a solution you can indicate that by clicking on the check mark next to that answer.
0 Likes 0 ·
Oleg avatar image
Oleg answered
Since you are asking specifically about how the TRANSFORM/PIVOT of Access translates into T-SQL PIVOT, here is the sample query based your input which is a more or less exact translation. The only small difference is that this one will probably display the zeroes for those months which don't have any records for the specified Dept/Specialty, but this is just a cosmetic change which you can add. The query below needs a parameter (just like your original query), so I hope that this is a close match. Please note that there is no need to explicitly group by the records because the pivot understands what need to be grouped implicitly, thus making the syntax somewhat simpler than its Access' counterpart.

declare @year int = 2013;

select 
    pvt.*, isnull(Jan, 0) + isnull(Feb, 0) + isnull(Mar, 0) + 
    isnull(Apr, 0) + isnull(May, 0) + isnull(Jun, 0) + 
    isnull(Jul, 0) + isnull(Aug, 0) + isnull(Sep, 0) + 
    isnull(Oct, 0) + isnull(Nov, 0) + isnull([Dec], 0) YTD
    from 
    (
        SELECT 
            s.[Site], d.[Dept/Specialty], l.[Language], 
            left(datename(month, h.[Date]), 3) [MonthName], 1 x
            FROM tbl_History h LEFT JOIN tbl_Sites s 
                ON h.[Site Code] = s.[Site Code]
            LEFT JOIN tbl_Dept d ON h.[Dept Code] = d.[Dept Code]
            LEFT JOIN l ON h.[Lang Code] = l.[Lang Code]
            WHERE 
                datepart(year, h.[Date]) = @year 
                and h.[Data from Mainframe] = 1
    ) src
    pivot (
        count(x) for [MonthName] in ([Jan], [Feb], [Mar], [Apr], [May], 
        [Jun], [Jul], [Aug], [Sep], [Oct], [Nov], [Dec])
    ) pvt
    order by 1, 2, 3;
    
Hope this helps, Oleg
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

THANK YOU! I will try it tomorrow - (and will add the missing table on the 2nd join: LEFT JOIN tbl_Language l ON h.[Lang Code] = l.[Lang Code]) Ernie
0 Likes 0 ·
Thank you Oleg! This looks great and provides the solution!
0 Likes 0 ·
Fatherjack avatar image
Fatherjack answered
The reports from you Access database should be replaced by Reporting Services reports. You will be able to use the same stored procedure to provide the data from the database to the report and then let the report format it as you want it. SSRS can be installed from the SQL Server installation media and it is really simple to develop great reports in BIDS (Business Intelligence Development Studio) if you are used to writing reports in Access.
8 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Or just attach the Excel spreadsheet to the database via ODBC ...
2 Likes 2 ·
Sounds like the timing is all wrong. They want you to get rid of the access front end before the SSRS replacement is available. No way to keep access around until the new sql server is ready? you can certainly use a query to pivot this, but it's going to be plain text and look bad compared to the report. maybe you can use DTS, or even an ad hoc query, to dump the raw data into excel and pivot it there. excel has an easy to use pivot function.
1 Like 1 ·
Our IT department will not install reporting services on our SQL Server 8.0 – they are working on a new SQL server which is not ready yet. Is there another way to output the info using a query? Google’d Access crosstab query and see some examples of PIVOT that might look good enough.
0 Likes 0 ·
Yes, PIVOT is the way to build a table of table like you have it in the image you included but SSMS isnt really the presentation layer and you will have issues to work around concerning NULLs and 0's. How about using Excel - either a Pivot Table or use the Powerpivot addin and you can format data much more nicely.
0 Likes 0 ·
That would be a bit smoother :)
0 Likes 0 ·
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.