how to use PIVOT table in sql server

Note : Using this row data will be act as column as well as column will be fit as row value, for more details refer below example

Table Structure with data

;with cts  -- Used CTE
as
(
select customer,Bhoonja,Biscuits,DahiVada
from (select customer,product,qty from items) up
pivot
(sum (qty) for product in(Bhoonja,Biscuits,DahiVada))as pvt
)
select cts.customer,isnull(cts.Bhoonja,0) as Bhoonja,
ISNULL(cts.DahiVada,0) as DahiVada,isnull(cts.Biscuits,0) as Biscuits,
isnull(cts.Bhoonja,0) + ISNULL(cts.DahiVada,0) + isnull(cts.Biscuits,0) as Total 
from cts order by cts.customer

After CTE execute (with Pivot Table)

Why dropdownlist binds limited data when we using JSON (from database)

Most of the time JSON max length exceeds (data) limit to occur this problem , try below coding in web.config to avoid these issues. Just increase the JSON length.

In the <configuration> section, add

<system.web.extensions>
    <scripting>
      <webServices>
        <jsonSerialization maxJsonLength="500000"></jsonSerialization>
      </webServices>
    </scripting>
  </system.web.extensions>

How to convert dataTable to List

List<Employee> emp = new List<Employee>();


DataTable dt = ViewState["CurrentEmp"] as DataTable;//Maintaining DataTable on ViewState For Demo only

//read data from DataTable using lamdaexpression


emp = (from DataRow row in dt.Rows

   select new Employee
   {
       _FirstName = row["FirstName"].ToString(),
       _LastName = row["Last_Name"].ToString()

   }).ToList();

return emp;