如何在SQL Server中以行方式获取列名和值 [英] How to get column names and values in row wise in SQL server
问题描述
我想以行方式显示列名和值。 I want the column names and values in row wise manner. 这篇关于如何在SQL Server中以行方式获取列名和值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
i想要绑定C＃服务器端的列名和值。
我尝试了一些查询但它只显示了列名，但是我想要相应的值。
我的表结构是< pre lang =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 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
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
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 < span class =code-digit> 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 < span class =code-digit> 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 < span class =code-keyword> 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
我希望输出像这种格式
座位否：状态：
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
我尝试了什么：
我尝试过以下查询但它只执行列名称。
alter procedure sp_Trial
varchar （max）
< span class =code-sdkkeyword> @ id
as
开始
DECLARE @collist VARCHAR （max）= ' '，
@sql NVARCHAR （max）
@ 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 + ' 和data = 0'
EXEC Sp_executesql
@ sql
end 我很确定你必须使用unpivot，请参阅：
使用PIVOT和UNPIVOT [ ^ ]
Unpivot Transformation [ ^ ]
SQL Server中的UNPIVOT示例 [ ^ ]
使用SQL Server的UNPIVOT运算符来帮助规范化输出 [ ^ ]
使用SQL Server的UNPIVOT运算符动态标准化输出 [ ^ ]
试试！
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 isId 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
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.
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
I'm pretty sure you have to use unpivot, see:
Using PIVOT and UNPIVOT[^]
Unpivot Transformation[^]
UNPIVOT example in SQL Server[^]
Use SQL Server's UNPIVOT operator to help normalize output[^]
Use SQL Server's UNPIVOT operator to dynamically normalize output[^]
Try!
