Cross-tab query
-
I'm trying to build a cross-tab query that is dynamic in mssql 2000. Since it does use the transform syntax I am stuck at doh The columnheader will always change. So it's more of a dynamic columnheader. Depending on the Day of the week. There could be 5 or 3 or 10 teacher who have classes to teacher at different time of the day. The Teacher's names will be the columnheader Teaher's names across the top The time is in the left column down and student name ____ Teacher_Name Teacher_Name Teacher_Name Teacher_Name Time Student_Name Student_Name Student_Name Student_Name Time Student_Name Student_Name Student_Name Student_Name Time Student_Name Student_Name Student_Name Student_Name C2 and R2 = Student_Name C3 and R2 = Student_Name C2 and R3 = Student_Name C1 and R3 = Student_Name and so on The only way i can build a cross-tab query is in crystal report. Any help would be gladly appreciated :(( I want to display the data in a listview or datagrid. I'm lossing hair here and i have a bit left. I'll look like homer in now time with 3 strings of hair. :mad:
-
I'm trying to build a cross-tab query that is dynamic in mssql 2000. Since it does use the transform syntax I am stuck at doh The columnheader will always change. So it's more of a dynamic columnheader. Depending on the Day of the week. There could be 5 or 3 or 10 teacher who have classes to teacher at different time of the day. The Teacher's names will be the columnheader Teaher's names across the top The time is in the left column down and student name ____ Teacher_Name Teacher_Name Teacher_Name Teacher_Name Time Student_Name Student_Name Student_Name Student_Name Time Student_Name Student_Name Student_Name Student_Name Time Student_Name Student_Name Student_Name Student_Name C2 and R2 = Student_Name C3 and R2 = Student_Name C2 and R3 = Student_Name C1 and R3 = Student_Name and so on The only way i can build a cross-tab query is in crystal report. Any help would be gladly appreciated :(( I want to display the data in a listview or datagrid. I'm lossing hair here and i have a bit left. I'll look like homer in now time with 3 strings of hair. :mad:
This is not a trivial task. If you are not up on T-SQL you may want to look at a alternative method. I initially created a cursor to create the a SQL statements as follows. Example:
SELECT tp.PropertyTestID, tp.PropertyTestGroupID, tp.PropertyTestName,
SUM(CASE WHEN S.SampleID = 122 THEN ResultDecimal ELSE NULL END) AS [N/A122 05/02/2004],
SUM(CASE WHEN S.SampleID = 109 THEN ResultDecimal ELSE NULL END) AS [N/A109 03/31/2004],
SUM(CASE WHEN S.SampleID = 108 THEN ResultDecimal ELSE NULL END) AS [N/A108 03/30/2004],
SUM(CASE WHEN S.SampleID = 107 THEN ResultDecimal ELSE NULL END) AS [N/A107 03/17/2004],
SUM(CASE WHEN S.SampleID = 106 THEN ResultDecimal ELSE NULL END) AS [N/A106 03/01/2004],
SUM(CASE WHEN S.SampleID = 105 THEN ResultDecimal ELSE NULL END) AS [N/A105 01/05/2004],
SUM(CASE WHEN S.SampleID = 104 THEN ResultDecimal ELSE NULL END) AS [N/A104 11/17/2003]
FROM tblSamplesPropertyTest tp,
tblSamplesSample s,
tblSamplesSampleResult sr,
tblSamplesCustomerProductReportPropertyTest prpt,
tblSamplesProductPropertyTest ppt
WHERE tp.PropertyTestID = ppt.PropertyTestID
AND ppt.PropertyTestID = prpt.PropertyTestID
AND sr.CustomerProductReportPropertyTestID = prpt.CustomerProductReportPropertyTestID
AND s.SampleID =* sr.SampleID
AND s.SampleID in (122,109,108,107,106,105,104)
AND tp.ResultTypeID = 1
AND (tp.Deleted = 0 or tp.Deleted Is Null)
GROUP BY tp.PropertyTestID, tp.PropertyTestGroupID, tp.PropertyTestNameAfter creating the statement I executed exec(@SQL) in the procedure. However, I found this site that was quite helpful. Once I figured out what the crosstab stored procedure was doing in the article. Now any new crosstab/pivot tables are created using this method. http://www.sqlteam.com/item.asp?itemID=2955 The easiest route, but not the best, may be to create an ODBC link to SQL server. Create an access database that links the necessary tables for the reports. Then simply create the reports in Access. Good Luck, Michael I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious. Vince Lombardi (1913-1970)
-
This is not a trivial task. If you are not up on T-SQL you may want to look at a alternative method. I initially created a cursor to create the a SQL statements as follows. Example:
SELECT tp.PropertyTestID, tp.PropertyTestGroupID, tp.PropertyTestName,
SUM(CASE WHEN S.SampleID = 122 THEN ResultDecimal ELSE NULL END) AS [N/A122 05/02/2004],
SUM(CASE WHEN S.SampleID = 109 THEN ResultDecimal ELSE NULL END) AS [N/A109 03/31/2004],
SUM(CASE WHEN S.SampleID = 108 THEN ResultDecimal ELSE NULL END) AS [N/A108 03/30/2004],
SUM(CASE WHEN S.SampleID = 107 THEN ResultDecimal ELSE NULL END) AS [N/A107 03/17/2004],
SUM(CASE WHEN S.SampleID = 106 THEN ResultDecimal ELSE NULL END) AS [N/A106 03/01/2004],
SUM(CASE WHEN S.SampleID = 105 THEN ResultDecimal ELSE NULL END) AS [N/A105 01/05/2004],
SUM(CASE WHEN S.SampleID = 104 THEN ResultDecimal ELSE NULL END) AS [N/A104 11/17/2003]
FROM tblSamplesPropertyTest tp,
tblSamplesSample s,
tblSamplesSampleResult sr,
tblSamplesCustomerProductReportPropertyTest prpt,
tblSamplesProductPropertyTest ppt
WHERE tp.PropertyTestID = ppt.PropertyTestID
AND ppt.PropertyTestID = prpt.PropertyTestID
AND sr.CustomerProductReportPropertyTestID = prpt.CustomerProductReportPropertyTestID
AND s.SampleID =* sr.SampleID
AND s.SampleID in (122,109,108,107,106,105,104)
AND tp.ResultTypeID = 1
AND (tp.Deleted = 0 or tp.Deleted Is Null)
GROUP BY tp.PropertyTestID, tp.PropertyTestGroupID, tp.PropertyTestNameAfter creating the statement I executed exec(@SQL) in the procedure. However, I found this site that was quite helpful. Once I figured out what the crosstab stored procedure was doing in the article. Now any new crosstab/pivot tables are created using this method. http://www.sqlteam.com/item.asp?itemID=2955 The easiest route, but not the best, may be to create an ODBC link to SQL server. Create an access database that links the necessary tables for the reports. Then simply create the reports in Access. Good Luck, Michael I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious. Vince Lombardi (1913-1970)