如何选择列作为行？ [英] how to select columns as rows?

问题描述

所以，我一直在搜寻，发现了与我的问题类似的东西，但是我需要更多帮助才能找到真正的解决方案。

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.

我在尝试构造一个将返回2列数据的查询时，第一列应该是列名称本身的列表，第二列应该是该列的值。

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.

在视觉上看起来像这样

Column1      Column2
-------      -------
columnA      value_of_columnA
columnB      value_of_columnB
...          ...

我非常确定这将需要动态SQL来实现，但是我不知道如何开始创建查询。

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.

任何帮助都是

推荐答案

这对任何表都适用，但是在我的示例中，我只是创建了一个测试表。您需要在@YourTableName中设置表名称。另外，您需要设置@YourTableWhere以将结果限制为一行，否则输出看起来很奇怪，因为多行混合在一起。

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.

请尝试以下操作：

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)

输出：

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

编辑

为了与SQL Server 2000兼容，您应该能够用varchar（8000）替换varchar（max）并使用它代替上面的代码中的 SELECT @YourQuery 查询：

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:

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

这篇关于如何选择列作为行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！