Group data from the last N years

Asked Oct 23 '17 at 07:59

Active Oct 23 '17 at 07:59

Viewed 34 times

I have the tables

create table classroom(classID int, name nvarchar(50), year int)
create table subject(subjectId int, subjectName nvarchar(50) )
create table Approved(answerId int, total int, classID int,subjectId int)

I want to get a query so I can list the results of the exams from the last N years (from now):

TITLE : # of students approved in : 15. History
classroom Name | 2013 | 2014 | 2015 | 2016 | 2017|
------------------------------------------------
1st grade     |   10  | 20   |  5   |  20  | 30
2st grade     |   3   | 50   |  5   |  60  | 40
3st grade     |   10  | 20   |  7   |  20  | 30

Is it possible to get this results using a procedure that expects as parameter the last N years from the current one?

asked Oct 23 '17 at 07:59

Flezcano

1,647
5
22
39

Where is your sql query? – Ullas Oct 23 '17 at 08:00
have no idea where to start. Even some guidelines about converting cell values (years) into columns will be appreciated . – Flezcano Oct 23 '17 at 08:02
And also, why would you want to do this is a procedure? By providing a variable number of columns almost every piece of software designed to use stored procedures as a data source will break as soon as you ask for a different number of years. – iamdave Oct 23 '17 at 08:03
2

@Flezcano you can start with dynamic sql + pivot. Or dynamic sql + case aggregates – Dmitrij Kultasev Oct 23 '17 at 08:03
2

Possible duplicate of [SQL Server dynamic PIVOT query?](https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) – cloudsafe Oct 23 '17 at 08:04
This is best dealt with in your presentation layer. Where are you wanting to display this data? – iamdave Oct 23 '17 at 08:04
Within a simple html table – Flezcano Oct 23 '17 at 08:05
@DmitrijKultasev thanks I will start there. – Flezcano Oct 23 '17 at 08:05
@Flezcano cloudsafe gave you the right link – Dmitrij Kultasev Oct 23 '17 at 08:06
Possible duplicate of [How to pivot table for year in SQL?](https://stackoverflow.com/questions/25201292/how-to-pivot-table-for-year-in-sql) – Hong Van Vit Oct 23 '17 at 08:10

Group data from the last N years

0 Answers0