Experience converting crosstab queries in Access 2007 to Pivot tables in SQL Server

[kryptkitten]

Hi All -
We are in the process of trying to move over a bunch of queries from Access 2007 to SQL Server 2008. I am having some difficulty trying to figure out how to convert the following code, which is a crosstab query in Access, to a pivot table in SQL Server. I believe this is one of the more complex ones.

TRANSFORM Count(dbo_v_CCP_SCCA_EventSummary.Event_Id) AS CountOfEvent_Id
SELECT dbo_vLabTestTypes.Problem, dbo_v_CCP_SCCA_EventSummary.Location_Service_Description
FROM dbo_v_CCP_SCCA_EventSummary INNER JOIN dbo_vLabTestTypes ON dbo_v_CCP_SCCA_EventSummary.Event_Id = dbo_vLabTestTypes.Event_ID
WHERE (((dbo_v_CCP_SCCA_EventSummary.Event_Type_Level_1)="Laboratory test"))
GROUP BY dbo_vLabTestTypes.Problem, dbo_v_CCP_SCCA_EventSummary.Location_Service_Description
PIVOT Year([Event_Occurence_Date]) & " Q" & DatePart("q",[Event_Occurence_Date]);

I have attached an Excel spreadsheet so you can see what the code produces.

I have been googling this to death without a whole lot of success so thought I would check here to see if anyone has had some success with making the conversion.

Thanks in advance for the assistance!

Stacy

 

[kryptkitten]

We've decided to go another approach and write just a standard T-SQL query and use the BIDS reporting component to work on the display of the data.

Much easier!

Thanks!

Stacy

 

[SQL_Hell]

Much better idea, personally I hate PIVOT tables in SQL server... they are confusing to write, stupidly un-dynamic and a ball ache to maintain.

Use matrix reports in SQL server reporting services

http://msdn.microsoft.com/en-us/library/ms157334(v=sql.100).aspx

 

[kryptkitten]

Yeah after two weeks of tearing my hair out and talking with some other developers a little more versed with SQL Server, they said the same thing. Thanks for the tip and will check that out! We have to get reporting services configured and then I should be on my way! Nearly finished with all my data validation which has taken much longer than it should have and then we can move forward!

Stacy