如何选择列作为行？

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!

任何帮助表示赞赏！

5 个解决方案

#1

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:

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 @YourQuery query 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

#2

select column_name,* from information_schema.columns
 where table_name = 'TheTableName'
order by ordinal_position

#3

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

#4

My answer to this question will 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功能。

#5

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 can write 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.

无论哪种方式，我认为你需要重新考虑你的方法。

#1