Convert Rows to Columns using C# logic with entity framework

The Students studying in BE Final Year. In Final year they have Four Theory Subjects.

List of Subjects and their max and passing marks are as below:

1. EC1 – Max marks 100 . Passing marks 30

2. EC2– Max marks 100 . Passing marks 30

3. EC3– Max marks 100 . Passing marks 30

4. EC4– Max marks 100 . Passing marks 30

5. EC5– Max marks 100 . Passing marks 30

The individual marks are stored in the table like below.

Table Name: Students

StudentID	StudentName	SubjectName	Marks
1	Savita	EC1	50
1	Savita	EC2	55
1	Savita	EC3	28
1	Savita	EC4	30
1	Savita	EC5	35

Problem:

1. You have to write a program to display the list of candidates along with their individual subject marks and total marks like below.

StudentName	EC1	EC2	EC3	EC4	EC5	Total
Savita	50	55	28	30	30	193
Harish	55	60	30	35	30	210

2. Add logic to your code such that this list of students displayed is in order. The person with maximum total marks is at the top and the person with the least marks is at the bottom.

StudentName	EC1	EC2	EC3	EC4	EC5	Total
Harish	55	60	30	35	30	210
Savita	50	55	28	30	30	193

3. Add a button [Show Results of All]. When clicked, displays the Pass or Fail status for all the students.

Logic is simple: If a student has failed in any subject, he is fail.

StudentName	EC1	EC2	EC3	EC4	EC5	Result	Total
Harish	55	60	30	35	30	Pass	210
Savita	50	55	28	30	30	Fail	193

4. Add a button [Show Results with Grace Marks]. When clicked, display Results with Grace Marks, the program has to add Grace Marks to failed students following a logic. Pass/Fail status has to be displayed for all the students after the Grace Marks are added.

Logic is: Total grace marks per student is max of 6 marks. If the candidate has failed in two subjects with 28 marks in EC1 and 27 marks in EC2, then after grace marks EC1 = 30 and EC2=30. The candidate is declared pass.

If the candidate has secured 25 marks in EC1 and 28 marks in EC2, then the total grace marks needed is 5+2 (7 marks). Hence the student is declared fail and no grace marks are given. Please note that Grace marks can be given to max of 2 subjects only. If Candidate has failed in more than 2 subjects then he is failed and no grace marks are given.

StudentName	EC1	EC2	EC3	EC4	EC5	Result	Total
Harish	55	60	30	35	30	Pass	210
Savita	50	55	30	30	30	Pass	195

Please Don’t use any SQL functions (stored procedure, views, pivot etc). All logic should be done in BLL

I would recommend to use pivot in sql server.

Firstly, you can create a view in database based on table for row to cloumn as sample:

Demo: https://data.stackexchange.com/stackoverflow/query/497432

CREATE table #yourtable
([Id] int, [Value] varchar(6), [ColumnName] varchar(13))
;
INSERT INTO #yourtable
([Id], [Value], [ColumnName])
VALUES
(1, 'John', 'FirstName'),
(2, '2.4', 'Amount'),
(3, 'ZH1E4A', 'PostalCode'),
(4, 'Fork', 'LastName'),
(5, '857685', 'AccountNumber')
;
select Firstname, Amount, PostalCode, LastName, AccountNumber
from
(
select value, columnname
from #yourtable
) d
pivot
(
max(value)
for columnname in (Firstname, Amount, PostalCode, LastName, AccountNumber)
) piv;

Secondly, use that view as table in entity framework.

I have wrote an article for that using view in entity framework.

https://www.c-sharpcorner.com/article/how-to-implement-database-views-using-entity-framework-ef-code-first-approach/

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

LAVANYA MUDENUR5y ago
Plz give me answer for this
+1
Rijwan Ansari5y ago
I would recommend to use pivot in sql server.
Firstly, you can create a view in database based on table for row to cloumn as sample:
Demo: https://data.stackexchange.com/stackoverflow/query/497432
CREATE table #yourtable
    ([Id] int, [Value] varchar(6), [ColumnName] varchar(13))
;

INSERT INTO #yourtable
    ([Id], [Value], [ColumnName])
VALUES
    (1, 'John', 'FirstName'),
    (2, '2.4', 'Amount'),
    (3, 'ZH1E4A', 'PostalCode'),
    (4, 'Fork', 'LastName'),
    (5, '857685', 'AccountNumber')
;


select Firstname, Amount, PostalCode, LastName, AccountNumber
from
(
  select value, columnname
  from #yourtable
) d
pivot
(
  max(value)
  for columnname in (Firstname, Amount, PostalCode, LastName, AccountNumber)
) piv;
Secondly, use that view as table in entity framework.
I have wrote an article for that using view in entity framework.
https://www.c-sharpcorner.com/article/how-to-implement-database-views-using-entity-framework-ef-code-first-approach/
+1
Rajanikant Hawaldar5y ago
Duplicate Thread

https://www.c-sharpcorner.com/forums/convert-row-data-to-column
+1
Suraj Dubey5y ago
Try this,

I hope this will help you

https://www.aspsnippets.com/Articles/Rotate-DataTable---Convert-DataTable-Columns-to-Rows-and-Rows-to-Columns-using-C-and-VBNet.aspx
+1