SQL 如何选择列作为行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1578500/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to select columns as rows?
提问by Shaded
So, I've been searching around and I've found things similar to my problem, but I need more help to get a real solution.
所以，我一直在四处寻找，发现了与我的问题类似的问题，但我需要更多帮助才能获得真正的解决方案。
I'm trying to construct a query that will return 2 columns of data, the first column should be a list of the column names themselves and the second should be the value of that column.
我正在尝试构建一个将返回 2 列数据的查询，第一列应该是列名本身的列表，第二列应该是该列的值。
Visually it would look like this
在视觉上它看起来像这样
Column1 Column2
------- -------
columnA value_of_columnA
columnB value_of_columnB
... ...
I'm pretty sure that this is going to require dynamic SQL to achieve, but I have no idea how to even begin creating the query.
我很确定这将需要动态 SQL 来实现，但我什至不知道如何开始创建查询。
Any help is appreciated!
任何帮助表示赞赏！
采纳答案by KM.
This should work for any table, but in my example I just create a test one. You need to set the table name within @YourTableName. Also, you need to set @YourTableWhere to limit the results to one row, otherwise the output looks strange with multiple rows mixed together.
这应该适用于任何表，但在我的示例中，我只创建了一个测试表。您需要在@YourTableName 中设置表名。此外，您需要设置 @YourTableWhere 将结果限制为一行，否则输出看起来很奇怪，多行混合在一起。
try this:
尝试这个：
BEGIN TRY
CREATE TABLE YourTestTable
(RowID int primary key not null identity(1,1)
,col1 int null
,col2 varchar(30)
,col3 varchar(20)
,col4 money
,StatusValue char(1)
,xyz_123 int
)
INSERT INTO YourTestTable (col1,col2,col3,col4,StatusValue,xyz_123) VALUES (1234,'wow wee!','this is a long test!',1234.56,'A',98765)
INSERT INTO YourTestTable (col1,col2,col3,col4,StatusValue,xyz_123) VALUES (543,'oh no!','short test',0,'I',12)
END TRY BEGIN CATCH END CATCH
select * from YourTestTable
DECLARE @YourTableName varchar(1000)
DECLARE @YourTableWhere varchar(1000)
DECLARE @YourQuery varchar(max)
SET @YourTableName='YourTestTable'
set @YourTableWhere='y.RowID=1'
SELECT
@YourQuery = STUFF(
(SELECT
' UNION '
+ 'SELECT '''+COLUMN_NAME+''', CONVERT(varchar(max),'+COLUMN_NAME+') FROM '+@YourTableName+' y'+ISNULL(' WHERE '+@YourTableWhere,'')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = @YourTableName
FOR XML PATH('')
), 1, 7, ''
)
PRINT @YourQuery
EXEC (@YourQuery)
OUTPUT:
输出：
RowID col1 col2 col3 col4 StatusValue xyz_123
----------- ----------- ------------------------------ -------------------- --------------------- ----------- -----------
1 1234 wow wee! this is a long test! 1234.56 A 98765
2 543 oh no! short test 0.00 I 12
SELECT 'RowID', CONVERT(varchar(max),RowID) FROM YourTestTable y WHERE y.RowID=1 UNION SELECT 'col1', CONVERT(varchar(max),col1) FROM YourTestTable y WHERE y.RowID=1 UNION SELECT 'col2', CONVERT(varchar(max),col2) FROM YourTestTable y WHERE y.RowID=1 UNION SELECT 'col3', CONVERT(varchar(max),col3) FROM YourTestTable y WHERE y.RowID=1 UNION SELECT 'col4', CONVERT(varchar(max),col4) FROM YourTestTable y WHERE y.RowID=1 UNION SELECT 'StatusValue', CONVERT(varchar(max),StatusValue) FROM YourTestTable y WHERE y.RowID=1 UNION SELECT 'xyz_123', CONVERT(varchar(max),xyz_123) FROM YourTestTable y WHERE y.RowID=1
----------- ------------------------
col1 1234
col2 wow wee!
col3 this is a long test!
col4 1234.56
RowID 1
StatusValue A
xyz_123 98765
EDIT
编辑
For SQL Server 2000 compatibility, you should be able to replace varchar(max) with varchar(8000) and use this in place of the SELECT @YourQueryquery from the code above:
对于 SQL Server 2000 兼容性，您应该能够用 varchar(8000) 替换 varchar(max) 并使用它代替SELECT @YourQuery上面代码中的查询：
SELECT
@YourQuery=ISNULL(@YourQuery+' UNION ','')
+ 'SELECT '''+COLUMN_NAME+''', CONVERT(varchar(max),'+COLUMN_NAME+') FROM '+@YourTableName+' y'+ISNULL(' WHERE '+@YourTableWhere,'')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = @YourTableName
回答by Chris Ballance
select column_name,* from information_schema.columns
where table_name = 'TheTableName'
order by ordinal_position
回答by kemiller2002
You could always do something like this
你总是可以做这样的事情
SELECT 'Column_Name' AS ColumnName,
(SELECT TOP 1 Column_Name FROM Table tbl2 WHERE tbl.ID = tbl2.ID)
FROM Table tbl
回答by Cade Roux
My answer to this questionwill work more easily with SQL Server 2000 because it doesn't use the XML features of SQL Server 2005.
我对这个问题的回答在SQL Server 2000 中更容易使用，因为它不使用 SQL Server 2005 的 XML 功能。
回答by J. Polfer
You aren't very clear about how you are presenting your report and what you are generating it with. Are you using direct results from the query tool to generate your "report"? In which case, methinks you are trying to pound a nail using a screwdriver. Use the right tool for the job.
您不太清楚如何呈现报告以及生成报告的内容。您是否使用查询工具的直接结果来生成“报告”？在这种情况下，我认为您正在尝试使用螺丝刀敲钉子。为工作使用正确的工具。
The SQL language, directly, shouldn't be used to setup your presentation data to generate your report. Really, it's a silly idea. The fact that you canwrite a report with straight-up SQL statements doesn't mean that you should.
不应直接使用 SQL 语言来设置演示数据以生成报告。真的，这是一个愚蠢的想法。您可以使用直接的 SQL 语句编写报告的事实并不意味着您应该。
You really ought to generate your report using an application program that you write yourself, or a report generation tool like Crystal Reports.
您确实应该使用您自己编写的应用程序或像 Crystal Reports 这样的报告生成工具来生成您的报告。
Application Program written by yourself:If you are using a cursor object to query the database, you can simply get the column names from that cursor object. Problem solved.
自己编写的应用程序：如果您使用游标对象查询数据库，您可以简单地从该游标对象中获取列名。问题解决了。
Report Generation Tool: usually they provide a facility to represent the dynamic data that could appear.
报告生成工具：通常它们提供一种工具来表示可能出现的动态数据。
Either way, I think you need to rethink your approach to this.
不管怎样，我认为你需要重新考虑你的方法。
