Hi
How to get a column names and values in row wise manner
I have a table which consists of 40columns like a1 a2 a3 .......a40.I want this columns in a row wise along with the values.
Seat No:
A1
A10
A11
A12
A13
A14
A15
A16
A17
A18
A19
A2
A20
A3
A5
A6
A7
A8
A9
S1
S10
S11
S12
S13
S14
S15
S16
S17
S18
S19
S2
S20
S3
S4
S5
S6
S7
S8
S9
The above output getting by the below procedure
CREATE 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 distinct SeatNo
FROM tbl_seat
CROSS apply (VALUES' + @collist
+ ') ca (seatno, data)
WHERE Routeid='+@id+' and data =0'
EXEC Sp_executesql
@sql
end
But i want the column values corresponding names for example
I want the result like below Output:
Seat No: Status:
A1 0
A10 0
A11 0
A12 0
A13
A14
A15
A16
A17
A18
A19
A2
A20
A3
A5
A6
A7
A8
A9
S1
S10
S11
S12
S13
S14
S15
S16
S17
S18
S19
S2
S20
S3
S4 0
S5 0
S6 0
S7 0
S8 0
S9 0