CODEWITHSUNDEEP
c#sqllinq
dhiraj sakariya
dhiraj sakariya

Reputation: 41

Transform some row into column in sql/ linq

I have the following query:

 SELECT 
     std. [Name]
     ,cl.Name as clsName
     ,stu.Name as student
     ,sub.Name as subject
     ,r.ObtainedMarks
  FROM [School.Model.SchoolContext].[dbo].[Standards] std
  join [School.Model.SchoolContext].[dbo].ClassSections cl on std.Id = cl.StandardId
  join [School.Model.SchoolContext].[dbo].Students stu on cl.id = stu.ClassSectionId
  join [School.Model.SchoolContext].[dbo].Subjects sub on std.id = sub.StandardId
  join [School.Model.SchoolContext].[dbo].Results  r on stu.Id = r.StudentId)

Which show following results:

Table

I would like to add two columns for subjects e.g. English and Maths and remove column Objects marks. The obtained marks should be shown under subject.

Please let me know how can I achieve through SQL query or LINQ in C#.

I have gone through Pivot queries in SQL, but I didn't get much.

Upvotes: 0

Views: 45

Answers (1)

Veljko89
Veljko89

Reputation: 1953

Try like this, it maybe won't work as i didn't test it out, but continue from here, PIVOT is the thing you wish for

  SELECT * FROM (
        SELECT 
            std. [Name]
            ,cl.Name as clsName
            ,stu.Name as student
            ,sub.Name as subject
            ,r.ObtainedMarks
        FROM [School.Model.SchoolContext].[dbo].[Standards] std
        join [School.Model.SchoolContext].[dbo].ClassSections cl on std.Id = cl.StandardId
        join [School.Model.SchoolContext].[dbo].Students stu on cl.id = stu.ClassSectionId
        join [School.Model.SchoolContext].[dbo].Subjects sub on std.id = sub.StandardId
        join [School.Model.SchoolContext].[dbo].Results  r on stu.Id = r.StudentId)
      ) SRC
    PIVOT
    (
        MAX(ObtainedMarks) FOR [Subject] IN ([English], [Maths])
    ) piv

Upvotes: 1

Related Questions