retrieve values from coumn name

Vasanth Jack

Hi,

I have a table in this i have to get only columns names.I got that column but now i want the values.

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

FROM tbl_seat

CROSS apply (VALUES' + @collist

+ ') ca (seatno, data)

WHERE Routeid=11 and data = 0 '

EXEC Sp_executesql

2 Replies

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