I have got a table called tblCar and there are ID, CarNumber and other 32 fields. 32 columns are filled as s1,s2,s3,.......,s32 since they are dedicated to remark whether it is for SEATS which are available(A), booking(B), and Sold(S).

I want to count available seats, booking seats and sold out seats from that database. how should i write a query?

Is there anyway to count from string S, B and A from 32 different columns in database?

What i want to show is like that..... S = 20, B = 10, A = 2;

SqlCommand cmd = new SqlCommand(
  "Select count(*)
   FROM tblCar
   WHERE s1= 'S' or s2= 'S' or s3= 'S'
   or s4= 'S' or s5= 'S' or s6= 'S'
   or s7= 'S' or s8= 'S' or s9= 'S'
   or s10= 'S' or s11= 'S' or s12= 'S'
   or s13= 'S' or s14= 'S' or s15= 'S'
   or s16= 'S' or s17= 'S' or s18= 'S'
   or s19= 'S' or s20= 'S' or s21= 'S'
   or s22= 'S' or s23= 'S' or s24= 'S'
   or s25= 'S' or s26= 'S' or s27= 'S'
   or s28= 'S' or s29= 'S' or s30= 'S'
   or s31= 'S' or s32= 'S' ", con
);
count += cmd.ExecuteNonQuery();

this is how I work for "Sold" field.but it showed only count = -128.i did initialized count as 0

Try this sql query:

SELECT SUM(CASE WHEN State = 'S' THEN 1 ELSE 0 END) AS Sold
, SUM(CASE WHEN State = 'B' THEN 1 ELSE 0 END) AS Booked
, SUM(CASE WHEN State = 'A' THEN 1 ELSE 0 END) AS Available
FROM
    (SELECT S1, S2, S3, S4, S5, S6
    FROM Seats) s
UNPIVOT
    (State FOR Seat IN (S1, S2, S3, S4, S5, S6)) AS rows;

Here SQL Fiddle.

In example I used only 6 columns, but in your case you need set for all 32 columns, but only one time

C# code:

using SqlConnection conn = new SqlConnection(yourConnectionString)
{
    StringBuilder query = new StringBuilder();
    query.AppendLine("SELECT SUM(CASE WHEN State = 'S' THEN 1 ELSE 0 END) AS Sold");
    query.AppendLine(", SUM(CASE WHEN State = 'B' THEN 1 ELSE 0 END) AS Booked");
    query.AppendLine(", SUM(CASE WHEN State = 'A' THEN 1 ELSE 0 END) AS Available");
    query.AppendLine("FROM ");
    query.AppendLine("(SELECT S1, S2, S3, S4, S5, S6, S7, S8");
    query.AppendLine("(,S9, S10, S11, S12, S13, S14, S15, S16");
    query.AppendLine("(,S17, S18, S19, S20, S21, S22, S23, S24");
    query.AppendLine("(,S25, S26, S27, S28, S29, S30, S31, S32");
    query.AppendLine("FROM Seats) s");
    query.AppendLine("UNPIVOT");
    query.AppendLine("(State FOR Seat IN (S1, S2, S3, S4, S5, S6, S7, S8");
    query.AppendLine("(,S9, S10, S11, S12, S13, S14, S15, S16");
    query.AppendLine("(,S17, S18, S19, S20, S21, S22, S23, S24");
    query.AppendLine("(,S25, S26, S27, S28, S29, S30, S31, S32)) AS rows;");

    conn.Open();
    using SqlCommand command = new SqlCommand(query.ToString(), conn)
    {
        DataTable data = new DataTable();
        data.Load(command.ExecuteReader());
        //then get your values
        Int32 avialable = 0;
        Int32 booked= 0;
        Int32 sold = 0;
        if(data.Rows.Count > 0)
        {
            available = (Int32)data(0)("Available");
            booked = (Int32)data(0)("Booked");
            sold = (Int32)data(0)("Sold");
        }
    }
}

more