October 1, 2007 at 2:32 pm
Basically I need to create a report in this format.
On the x axis, needs to be a list of all of our offered courses (I work at a school) so it would look like
Sd 101 sd 102 sd 103 ect... There are 500+ courses
On the Y axis needs to be
PeopleId FirstName LastName Grade
I have a report with all the people i need, and i have a report with all the avaialble classes, but i have NO idea if/how i can pull this data to create the format i need. In our tables the class lists are stored in a column, I cant filter based on classes taken, because i need a list to display ALL avaialbe classes whether taken or not and this list needs to have the grade recieved for that class in the appropriate column. So the output would look like
---------------------Sd 101------------ sd 102-----------------sd 104
1234 John Doe ---------A+
1253 Jane Doe --------------------------D
1242 JOsh Doe ----------B+ -------------------------------------A-
Ect...
October 1, 2007 at 2:37 pm
October 1, 2007 at 2:39 pm
Currently im just pulling data FROM existing tables, Do i need to create a new table to get this format?
Im not looking to load this data INTO a table, i just need it to BE FORMATTED in a vary particular way. I have the 2 files i need, one listing all available classes, and one listing the students with their ids....
October 1, 2007 at 2:42 pm
October 1, 2007 at 2:46 pm
To get a distinct list of classes i used
select distinct event_id
from event
to get the people i needed (alums in spring 07) I used.
select distinct p.first_name
,p.last_name
,p.people_code_id
,td.event_id
,td.final_grade
from people as p
inner join academic as a
on a.people_code_id=p.people_code_id
inner join peopletype as pt
on a.people_code_id=pt.people_code_id
inner join transcriptdetail as td
on p.people_code_id=td.people_code_id
where p.deceased_flag='n'
and pt.people_type='alum'
and a.graduated_year='2007'
and a.graduated_term='spring'
^^
This will give me all the classes and grades that our 2007 spring alums took. As you can imagine its well over 5000 entries.
October 1, 2007 at 2:54 pm
October 1, 2007 at 2:59 pm
October 1, 2007 at 3:11 pm
You could try to search these forums for "crosstab" or "pivot". There are lots of similar questions, but I have to say that with your 500 or more columns in the resultset I'm not sure whether this can be done in some acceptable way on the server. Maybe you should generate standard resultset from SQL Server (student, course, grade) and then do this transformation into multicolumn table with some other tool.
Or maybe you have SQL Server 2005, where you can use PIVOT command (not available in SQLS2000 - and you posted on forum for 7/2000)?
To be honest, I don't see any sense in producing a table with 500+ columns. Do you think anyone will be able to view such report and get some meaningful info from it? I doubt that very much.
October 1, 2007 at 3:27 pm
I have done this with 360 columns and it works. You will not be able to use the dynamic solutions on the boeard because your select will run over 8000 characters.
What you will need to use is
select name,id ...,
max(case when td.event_id = 'sd101' then grade else null end) as [sd101],
max(case when td.event_id = 'sd102' then grade else null end) as [sd102],
max(case when td.event_id = 'sd102' then grade else null end) as [sd102],
and so on for each course...
from (alias your query here)
group by name,id ...
This is a lot of typing but we can build some sql to build your sql.
October 1, 2007 at 3:48 pm
^^ That might work, let me try it with some testing.....
Why use max?
October 1, 2007 at 4:11 pm
This seems to work just as well, any reason I NEED the max statement?
case when td.event_id = 'sd 099' then final_grade else null end as 'sd 099'
,case when td.event_id = 'sd 102' then final_grade else null end as 'sd 102'
Also, anyway to get the data on a single line for all the courses instead of this
Danieldoe12345NULLNULLNULLNULLNULLNULLNULL
Danieldoe12345NULLNULLNULLNULLNULLNULL2.4
Danieldoe12345NULLNULLNULLNULLNULL1.3NULL
Danieldoe12345NULLNULLNULLNULLNULL2.0NULL
Danieldoe12345NULLNULLNULLNULL2.1NULLNULL
Danieldoe12345NULLNULLNULL2.0NULLNULLNULL
Danieldoe12345NULLNULL2.0NULLNULLNULLNULL
Danieldoe12345NULL2.4NULLNULLNULLNULLNULL
October 1, 2007 at 4:26 pm
select name, min(case when td.event_id = 'sd 099' then final_grade else null end) as 'sd 099'
, min(case when td.event_id = 'sd 102' then final_grade else null end) as 'sd 102'
from mytable
Group by name
October 1, 2007 at 4:42 pm
same issue as above ^^
Why do you use min or max? My case without either seems to work.
October 1, 2007 at 5:17 pm
October 1, 2007 at 5:30 pm
Can you explain in MORE detail why i needed the max/min? It WORKED, using this
select distinct p.first_name
,p.last_name
,p.people_code_id
,max(case when td.event_id = 'sd 101' then final_grade else null end) as 'sd 101'
,max(case when td.event_id = 'sd 102' then final_grade else null end) as 'sd 102'
,max(case when td.event_id = 'sd 103' then final_grade else null end) as 'sd 103'
,max(case when td.event_id = 'sd 104' then final_grade else null end) as 'sd 104'
,max(case when td.event_id = 'sd 105' then final_grade else null end) as 'sd 105'
,max(case when td.event_id = 'sd 106' then final_grade else null end) as 'sd 106'
from people as p
inner join academic as a
on a.people_code_id=p.people_code_id
inner join peopletype as pt
on a.people_code_id=pt.people_code_id
inner join transcriptdetail as td
on p.people_code_id=td.people_code_id
where p.deceased_flag='n'
and pt.people_type='alum'
and a.graduated_year='2007'
and a.graduated_term='spring'
group by p.first_name
,p.last_name
,p.people_code_id
But im still not sure why i had to use max and how that created the single line. Thanks.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply