Vasanth Jack

Vasanth Jack

  • 1.7k
  • 289
  • 23.9k

How to get column names and values in row wise in sql Server

Jan 16 2017 4:46 AM
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.
 
This is my  Query
 
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
 

Answers (6)