MS SQL: Converting row printing to column based printing

[dfs3000my]

Hi, I am doing a student progression system where the application is able to keep track of student marks. I have a problem currently and I've been spending nights on this.

Let me briefly explain. Marks are calculated based on their hurdles. So for instance, H1 = xx%, H2 = xx%. The number of hurdles vary for different subjects. The code below is able to provide me the calculated marks based on each individual's hurdles in rows. However, I'm trying to output it in a horizontal format. Meaning:

Instead of:

xx%
xx%

I want it to be: xx% xx% (in two columns)

I've tried using the CASE clause in my SELECT section but that only works if the hurdles are static and fixed. My problem is the hurdles are dynamic as well. Is there anyone who has some idea on how to workaround this issue? Thanks in advance guys!! :)

SELECT     SUM(Marks.marks_value) * Hurdle.hurdle_weight / 100 AS total
FROM         Marks INNER JOIN
                      Student ON Marks.student_id = Student.student_id INNER JOIN
                      Class ON Student.student_id = Class.student_id INNER JOIN
                      MarkingCriteria ON Marks.criteria_id = MarkingCriteria.criteria_id INNER JOIN
                      AssessmentComponent ON MarkingCriteria.component_id = AssessmentComponent.component_id INNER JOIN
                      Hurdle ON AssessmentComponent.hurdle_id = Hurdle.hurdle_id
GROUP BY Hurdle.hurdle_id, Hurdle.hurdle_weight, Student.student_id, Class.student_id
ORDER BY Hurdle.hurdle_id

[sknake]

You shouldn't pivot a table (rows -> columns) dynamically. Lets say one person does 10k hurdles where the rest only have done 3... then everyone will have 9k columns of NULLs since they didn't have nearly enough data to pivot. You should probably tackle this issue at the application level and return your data as rows.

[dfs3000my]

sknake: i am restricting the number of hurdles to 5 at the client level so i'm sure it wouldn't be much of a problem. if i am insisting on my design, how should i make it work? I can't use cross tabs as the hurdle_ids are dynamic.

[sknake]

I posted an example of how to pivot a table in:
http://www.daniweb.com/forums/thread233907.html

I'm afraid your table setup is a little to complex to duplicate on the spot as I don't understand the relationships.

Here is the code from that thread:

IF OBJECT_ID('tempdb..#Parts', 'U') IS NOT NULL DROP TABLE #Parts
Create Table #Parts
(
  ID int,
  ID2 int,
  PartNo varchar(10)
)
SET NOCOUNT ON
Insert Into #Parts (ID, ID2, PartNo) Values (75, 23921, 'DENT')
Insert Into #Parts (ID, ID2, PartNo) Values (75, 26145, 'PLGD')
Insert Into #Parts (ID, ID2, PartNo) Values (75, 26145, 'PRRP')
Insert Into #Parts (ID, ID2, PartNo) Values (75, 26145, 'SIST')
Insert Into #Parts (ID, ID2, PartNo) Values (75, 57290, 'PRMD')
Insert Into #Parts (ID, ID2, PartNo) Values (75, 57290, 'abc')
Insert Into #Parts (ID, ID2, PartNo) Values (75, 57290, 'def')
Insert Into #Parts (ID, ID2, PartNo) Values (75, 57290, 'ghi')
Insert Into #Parts (ID, ID2, PartNo) Values (75, 57290, 'jkl')
SET NOCOUNT OFF

Select ID, ID2,
Max((Case When RowNumber = 1 Then PartNo Else '' End)) As Part1,
Max((Case When RowNumber = 2 Then PartNo Else '' End)) As Part2,
Max((Case When RowNumber = 3 Then PartNo Else '' End)) As Part3,
Max((Case When RowNumber = 4 Then PartNo Else '' End)) As Part4
From
(
  Select ID, ID2, PartNo,
  ROW_NUMBER() OVER(PARTITION BY ID, ID2 ORDER BY PartNo ASC) AS RowNumber
  From #Parts
) As tbl
Group By ID, ID2

Results:

ID          ID2         Part1      Part2      Part3      Part4
----------- ----------- ---------- ---------- ---------- ----------
75          23921       DENT                             
75          26145       PLGD       PRRP       SIST       
75          57290       abc        def        ghi        jkl

(3 row(s) affected)

[dfs3000my]

hmm, let me get this right. First you are creating a temp table to store the data to replicate the problem. For my case, I do not need to create a temp table right?

Next, your insert statements are inserting to the temp table.

And finally, your selecting it out from those table?

SELECT     Student_1.student_id, SUM(Marks.marks_value) * Hurdle.hurdle_weight / 100 AS total
FROM         Marks INNER JOIN
                      Student AS Student_1 ON Marks.student_id = Student_1.student_id INNER JOIN
                      Class AS Class_1 ON Student_1.student_id = Class_1.student_id INNER JOIN
                      MarkingCriteria ON Marks.criteria_id = MarkingCriteria.criteria_id INNER JOIN
                      AssessmentComponent ON MarkingCriteria.component_id = AssessmentComponent.component_id INNER JOIN
                      Hurdle ON AssessmentComponent.hurdle_id = Hurdle.hurdle_id
GROUP BY Hurdle.hurdle_id, Hurdle.hurdle_weight, Student_1.student_id

And my output is this:

1 31.4
1 60
4 0.2
4 39.6

'total ' column is what I am trying to make pivot.

[sknake]

The temp tables are just for simulating data so you could run the solution "as-is" to see how it works. You should only care about the selecting portion.

[dfs3000my]

I have managed to get the output that I want and below is my code. It may look pretty messy but well, that's the way it is :)

Thanks sknake for your help!

SELECT    student_name, student_number, student_email, 
   Max((CASE WHEN RowNumber = 1 THEN total ELSE '' END)) AS Hurdle1, 
   Max((CASE WHEN RowNumber = 2 THEN total ELSE '' END)) AS Hurdle2, 
   Max((CASE WHEN RowNumber = 3 THEN total ELSE '' END)) AS Hurdle3, 
   Max((CASE WHEN RowNumber = 4 THEN total ELSE '' END)) AS Hurdle4, 
   Max((CASE WHEN RowNumber = 5 THEN total ELSE '' END)) AS Hurdle5

FROM    (SELECT     Student.student_id, Student.student_name, Student.student_number, Student.student_email, 
   ROW_NUMBER() OVER (PARTITION BY Student.student_id ORDER BY Student.student_id) AS RowNumber,   
   SUM(Marks.marks_value) * Hurdle.hurdle_weight / 100 AS total
   FROM         Marks INNER JOIN
                      Student ON Marks.student_id = Student.student_id INNER JOIN
                      Class ON Student.student_id = Class.student_id INNER JOIN
                      MarkingCriteria ON Marks.criteria_id = MarkingCriteria.criteria_id INNER JOIN
                      AssessmentComponent ON MarkingCriteria.component_id = AssessmentComponent.component_id INNER JOIN
                      Hurdle ON AssessmentComponent.hurdle_id = Hurdle.hurdle_id
   GROUP BY Hurdle.hurdle_id, Hurdle.hurdle_weight, Student.student_id, Student.student_name, Student.student_number,   
   Student.student_email) AS derive

GROUP BY student_id, student_name, student_number, student_email

Output:
StudentName StudentNumber StudentEmail Hurdle1 Hurdle2 Hurdle3 Hurdle4 Hurdle5
Adam 0120R22222 [e-mail] 31.5 60 0 0 0
James 4120R22222 [e-mail] 0.2 39.6 0 0 0