A list in the column header with ASP NET and Linq

Question

I have this structure

and i want to get the following result

this means that i need to put the list of job in the header with Linq / C# , can you guys help with it ? i hope that my question is clear :)

The reality I'm afraid is that you need to show some effort / work you have done... that is why you are being marked down... http://www.codeproject.com/Articles/26657/Simple-LINQ-to-SQL-in-C — Paul Zahra, Aug 25 '16 at 08:18
I need to know how can i do it in SQL , just put me in the right way, how can i put the list of job in the header ? is Group by , .... any function could help, i even don't know how can i look for it in google :( — user1187282, Aug 25 '16 at 08:27
simple answer is you cant use it in sql. you have to write code to create this structure in a datatable — Sujit.Warrier, Aug 25 '16 at 08:38

score 1 · Answer 1 · edited May 23 '17 at 10:33

Acually you're looking for PIVOT MS SQL function check this answer for example.

You can do it without PIVOT like this:

SELECT 
   p.PersonName, 
   SUM(CASE WHEN j.IdJob IS NULL THEN 0 ELSE 1 END ) [IT],
   SUM(CASE WHEN j1.IdJob IS NULL THEN 0 ELSE 1 END ) [Teacher],
   SUM(CASE WHEN j2.IdJob IS NULL THEN 0 ELSE 1 END ) [Doctor]
FROM Person p
   LEFT OUTER JOIN Job j ON j.IdJob = p.IdJob AND j.JobName = 'IT'
   LEFT OUTER JOIN Job j1 ON j1.IdJob = p.IdJob AND j1.JobName = 'Teacher'
   LEFT OUTER JOIN Job j2 ON j2.IdJob = p.IdJob AND j2.JobName = 'Doctor'
GROUP BY p.PersonName

Anyway there is no simple clear way to make PIVOT table without enumerating all column names.

A list in the column header with ASP NET and Linq

1 Answers1