Click here to Skip to main content
15,446,510 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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
SQL
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

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

SQL
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
Posted
Updated 16-Jan-17 19:59pm

1 solution

 
Share this answer
 
Comments
Member 12857356 17-Jan-17 2:32am    
Do u have any Examples
Maciej Los 17-Jan-17 2:43am    
Did you follow the links? There's tons of examples! Don't be lazy!
Member 12857356 17-Jan-17 2:39am     CRLF
Thanks But i want all the column names and values in row wise
Maciej Los 17-Jan-17 2:46am    
That's the reason why Unpivot has been created.
Member 12857356 17-Jan-17 2:52am    
Thank you itz working

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900