MS SQL

MS SQL RSS

MS SQL: Converting row printing to column based printing

Please support our MS SQL advertiser: Intel Parallel Studio Home

Thread Solved

•

Join Date: Dec 2008

Posts: 19

Reputation: dfs3000my is an unknown quantity at this point

Solved Threads: 0

dfs3000my

Offline

Newbie Poster

MS SQL: Converting row printing to column based printing

Oct 29th, 2009

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!!

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
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
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

•

Join Date: Feb 2009

Posts: 3,258

Reputation: sknake has much to be proud of

Solved Threads: 581

sknake

Offline

.NET Enthusiast

Oct 29th, 2009

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.

Scott Knake
Custom Software Development
Apex Software, Inc.

•

Join Date: Dec 2008

Posts: 19

Reputation: dfs3000my is an unknown quantity at this point

Solved Threads: 0

dfs3000my

Offline

Newbie Poster

Oct 29th, 2009

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.

•

Join Date: Feb 2009

Posts: 3,258

Reputation: sknake has much to be proud of

Solved Threads: 581

sknake

Offline

.NET Enthusiast

Oct 29th, 2009

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:

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
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
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:

Help with Code Tags

text Syntax (Toggle Plain Text)

ID          ID2         Part1      Part2      Part3      Part4
----------- ----------- ---------- ---------- ---------- ----------
75          23921       DENT                             
75          26145       PLGD       PRRP       SIST       
75          57290       abc        def        ghi        jkl
 
(3 row(s) affected)

Scott Knake
Custom Software Development
Apex Software, Inc.

•

Join Date: Dec 2008

Posts: 19

Reputation: dfs3000my is an unknown quantity at this point

Solved Threads: 0

dfs3000my

Offline

Newbie Poster

Oct 29th, 2009

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?

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
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
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.

Last edited by dfs3000my; Oct 29th, 2009 at 5:46 am.

•

Join Date: Feb 2009

Posts: 3,258

Reputation: sknake has much to be proud of

Solved Threads: 581

sknake

Offline

.NET Enthusiast

Oct 29th, 2009

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.

Scott Knake
Custom Software Development
Apex Software, Inc.

•

Join Date: Dec 2008

Posts: 19

Reputation: dfs3000my is an unknown quantity at this point

Solved Threads: 0

dfs3000my

Offline

Newbie Poster

Oct 29th, 2009

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!

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
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
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 adam@hotmail.com 31.5 60 0 0 0
James 4120R22222 james@hotmail.com 0.2 39.6 0 0 0

Last edited by dfs3000my; Oct 29th, 2009 at 6:20 am.