Convert Access Transform/Pivot ("crosstab") query to SQL Server 2012

Question

How do I convert the following Access crosstab query to SQL Server 2012? From what I understand TRANSFORM and PIVOT can't be used in Sql Server but I am not sure on how to break down this code below:

TRANSFORM 
Count(Stats.SessionNumber) AS CountOfSessionNumber
SELECT 
Semesters.Semester, TermOptions.StudentDesc, 
Count(Stats.SessionNumber) AS TotalSessions
FROM Semesters, 
(StudentList_tbl 
INNER JOIN 
((StudentSemestersAndTerms 
INNER JOIN 
Stats ON (StudentSemestersAndTerms.StudentID = Stats.StudentID) 
AND (StudentSemestersAndTerms.Semester = Stats.Semester)) 
INNER JOIN 
TermOptions ON StudentSemestersAndTerms.Q3 = TermOptions.TermID) 
ON StudentList_tbl.StudentID = StudentSemestersAndTerms.StudentID) 
INNER JOIN 
TrainersList ON StudentList_tbl.RTP = TrainersList.RTPID
WHERE 
(((Stats.Semester)<[Semesters].[Semester]))
GROUP BY 
Semesters.Semester, TermOptions.StudentDesc
PIVOT 
TrainersList.ShortName;

sample data:

-------------------------------------------------------------------------
| Semester | StudentDesc | TotalSessions | FTL  |  GTY  |  MAU  |  CYU  |
-------------------------------------------------------------------------
|        3 | Term 1      |          1420 |  250 |  100  |  50   |  1020 |
-------------------------------------------------------------------------
|        3 | Term 2      |           700 |  200 |   25  |  75   |  300  |
-------------------------------------------------------------------------
|        3 | Term 3      |          1400 |      |   50  | 50    |  1300 |
-------------------------------------------------------------------------

The tables are:
Semesters  
StudentList_tbl
StudentSemestersAndTerms 
Stats
TermOptions
TrainersList

Row Heading
Semesters.Semester
TermOptions.StudentDesc
TrainersList.Shortname

Count
Stats.SessionNumber as TotalSessions

can you post the code to create sample tables and sample rows ? — Erran Morad, Jun 10 '14 at 04:21
The sample data above shows a partial display of what the output will be — maggs, Jun 10 '14 at 05:58
Output means the end result you are expecting. Sample data is the input data, i.e all the tables and rows you are using. I hope it is clear what I am asking for. — Erran Morad, Jun 10 '14 at 06:01
Borat I have added the tables, Row Headings and Count. Do you need anything else? Thanks — maggs, Jun 10 '14 at 06:40
The tables are there, but where are the sample rows. I think I mentioned everything so clearly. I wonder what is confusing. Anyway, I am going to rest now. Good luck. — Erran Morad, Jun 10 '14 at 06:43
possible duplicate of [Convert an Access crosstab query to T-SQL (SQL Server)](http://stackoverflow.com/questions/19410626/convert-an-access-crosstab-query-to-t-sql-sql-server) — Gord Thompson, Jun 10 '14 at 12:11

Convert Access Transform/Pivot ("crosstab") query to SQL Server 2012

0 Answers0