I am trying to convert some pivot queries from MS Access to MS SQL Server. I'm almost there, but not completely as it has to be.
This is the SQL code from MS Access:
TRANSFORM Min(q3TreatmentRelevant.TreatStart) AS MinOfTreatStart
SELECT q3TreatmentRelevant.CoursePatID, Min(q3TreatmentRelevant.TreatStart) AS FirstTreatStart, Count(q3TreatmentRelevant.SiteID) AS NumberOfTreatments
GROUP BY q3TreatmentRelevant.CoursePatID
And here's what I got to with SQL Server:
SELECT * FROM
MIN(dbo.view_3TreatmentRelevant.TreatStart) AS FirstTreatStart,
dbo.view_3TreatmentRelevant.CoursePatID, dbo.view_3TreatmentRelevant.PatSiteID, dbo.view_3TreatmentRelevant.TreatmentDrugHighLevel
) AS BaseData
MIN(BaseData.FirstTreatStart) FOR TreatmentDrugHighLevel IN ([Check-point-inhib]
) AS PivotTable
ORDER BY CoursePatID
The result looks like this (sorry for lack of allignment):
CoursePatId NumberOfTreatments Check-point-inhib Cytokine Cytostatic drug Targeted
a 1 2010-10-27 00:00:00.000 NULL NULL NULL
a 2 NULL NULL 2010-03-15 00:00:00.000 NULL
b 1 2010-06-23 00:00:00.000 NULL NULL NULL
c 1 2010-07-01 00:00:00.000 2008-12-01 00:00:00.000 NULL NULL
1) I want to group by CoursePatId only and get only 1 line for each, but it split up for several ones like the "a".
2) I don't like using static values inside the "PIVOT FOR IN" clause. I would prefer dynamic values from a SELECT statement, but that doesn't work. How can I do this?
3) All of it gotta be a view. Is that possible considering 2), or will I have to make a stored procedure?