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 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