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

Vasanth Jack

9y7.2kLearn iOS Programming

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)

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

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke9y ago
Accepted answer
You still haven't answered my question, in which object you want it. Anyway.
Just write a normal select in a sql, and use ExecuteReader to get the SqlDataReader in C#
using(SqlDataReader dr = cmd.ExecuteReader())
{
Int32 columnCount = dr.FieldCount;

while(dr.Read()) // Assuming there is only one row.
{
for(Int32 columnIndex = 0; columnIndex < columnCount; columnIndex++)
{
Console.WriteLine(dr.GetName(columnIndex)) + ": " + dr.GetValue(columnIndex).ToString();
}
}
dr.Close();
}
Now you have column name and it's value next to it each on a separate line.

Instead of Console.WriteLine above, just put these two values any other object you want, for example collection or a List etc.
+2
Vasanth Jack9y ago
@nithin

I want the column names and column values in row wise
0
Nitin Sontakke9y ago
@Vasanth,

It is not clear to whom your reply addressed to.

In which type of object you want it. Please specify. Data Table, Collection? I am assuming you are getting one row of result. Is that right?

It is very easy to achieve it with Getname function. Just paste your simple select statement on db side and i shall give you code. Not the complete but the hint.
0
Vasanth Jack9y ago
Thanks,

I want all the column name and Values in Row wise.But in your examples doesnot work .
0
Nitin Sontakke9y ago
If there is code involved in C# side, why not just execute the DataReader and get column names on C# side by calling dr.GetName(columnOrdinal)?

Refer to this: https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.getname(v=vs.110).aspx

This is the simplest approach.
+1
Saillesh Pawar9y ago
Try Pivot please check below links:

http://sqlhints.com/2014/03/10/pivot-and-unpivot-in-sql-server/
https://www.youtube.com/watch?v=h3BtudZehuo
0