How to get column names and values in row wise in SQL server

See more:

ASP.NET

SQL-Server-2008R2

I want the column names and values in row wise manner.

i want to bind the Column names and values in C# server side.

I tried some Query But it shows only Column names ,But i want the corresponding values.

and my table structure is

Id          RouteId     A1    A2    A3    A5    A6    A7    A8    A9    A10   A11   A12   A13   A14   A15   A16   A17   A18   A19   A20   S1    S2    S3    S4    S5    S6    S7    S8    S9    S10   S11   S12   S13   S14   S15   S16   S17   S18   S19   S20   A4
----------- ----------- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- -----
5           10          0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     NULL  NULL  NULL  NULL
6           11          0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     NULL
7           12          0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     NULL  NULL
8           13          0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     NULL  NULL  NULL
9           14          0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     NULL  NULL  NULL
10          15          0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     NULL  NULL  NULL  NULL
11          16          0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     NULL  NULL  NULL  NULL  NULL  NULL  NULL
12          17          0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     NULL  NULL  NULL  NULL  NULL  NULL  NULL
13          18          0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     NULL  NULL  NULL  NULL  NULL  NULL  NULL  NULL  NULL
14          19          0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     NULL  NULL  NULL  NULL  NULL  NULL  NULL  NULL  NULL
15          20          0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     0     NULL  NULL  NULL  NULL  NULL  NULL  NULL  NULL  NULL

I want the output like this format

Seat No:   Status:
   A1           0
   A10          0
   A11          0
   A12          0
   A13          0
   A14          0
   A15          0
   A16          0
   A17          0
   A18          0
   A19          0
   A2           0
   A20
   A3
   A5
   A6
   A7
   A8
   A9
   S1
   S10
   S11
   S12
   S13
   S14
   S15
   S16
   S17
   S18
   S19
   S2
   S20       0
   S3        0
   S4        0
   S5        0
   S6        0
   S7        0
   S8        0
   S9        0

What I have tried:

I tried the Below query But it exectes only column names.

alter procedure sp_Trial
@id varchar(max)
as
begin
    DECLARE @collist VARCHAR(max)='',
    @sql NVARCHAR(max)
    SELECT 
        @collist += '(''' + COLUMN_NAME + ''',' + COLUMN_NAME + '),'
    FROM INFORMATION_SCHEMA.columns
    WHERE TABLE_NAME = 'tbl_seat'
    --AND COLUMN_NAME LIKE 'Field%'
    AND TABLE_SCHEMA = 'dbo'
    SELECT @collist = LEFT(@collist, Len(@collist) - 1)
    SET @sql ='
                SELECT SeatNo
                FROM tbl_seat
                CROSS apply (VALUES' + @collist
                + ') ca (seatno, data)
                WHERE Routeid='+@id+' and data =0'
    EXEC Sp_executesql
    @sql
end