Sql server：根据记录值返回列名 [英] Sql server:return column names based on record values

问题描述

我有一张桌子。我想根据列值列出列名

Hi,


I have one table.I want tha column names based on column values

|Field1 |Field2 |Field3 |Field4 |
|-------|-------|-------|-------|
| 1     | 0     | 0     | 1     |

现在我希望字段名称包含零。这是sql server中的这个POssible。



我尝试了什么：



我一直在努力做各种连接针对sys.columns（和sys.tables），但到目前为止还没什么用。

now i want the field name contain zero.Is this POssible in sql server.

What I have tried:

I've been trying to do various joins against sys.columns (and sys.tables), but so far to little avail.

推荐答案

即使可能，也可以根据数据上的值修改列名通常不会这样做。这会使调用端的编码变得更加困难，因为程序不再知道名称，因此引用结果集中的列会变得更难，在某些情况下甚至是不可能的。



另一件事是，如果结果集包含两行并且同一列的值在这些行上有所不同怎么办？在这种情况下，列名应该是什么？





一种方法是获取数据和基于此创建具有自定义列名称的查询。



但是为了做到这一点，你需要一次获取数据来确定列名，然后再次获取数据以使其具有自定义列名。正如所指出的，这没有意义。例如，将数据提取到客户端然后根据需要修改显示名称会更容易...



演示一个变体如何实现这一点考虑以下。但如上所述，我真的不推荐这个。



创建测试数据

Even if possible, modifying the column names based on the values on the data is not typically done. This would make the coding on the calling side much harder since the program doesn't know the names anymore so referencing the columns in the result set becomes harder, in some cases even impossible.

Another thing is that what if the result set contains two rows and the values for the same column are different on these rows? What should the column name be in such case?


One way to do this would be to fetch the data and based on that create the query with custom column names.

But in order to do that you would need to fetch the data one time for deciding the column names and then fetch the data again to have it with custom column names. As pointed out this wouldn't make sense. It would be much easier for example to fetch the data to the client and then modify the display names as needed...

To demonstrate one variation how this could be achieved consider the following. But as said, I really don't recommend this.

Create the test data

CREATE TABLE ColumNameTest (
col1 int,
col2 int);

INSERT INTO ColumNameTest (col1, col2) VALUES
(1,2),
(3,null),
(null,6);

创建并运行查询

Create and run the query

DECLARE @query nvarchar(max)
DECLARE @alias nvarchar(max)
SET @query = ' SELECT '

SELECT @alias = STUFF((SELECT ',' + COALESCE(CAST(col1 AS nvarchar(100)), 'null') FROM columnametest FOR XML PATH('')),1,1,'');
SET @query = @query + ' col1 AS [' + @alias + ']'

SELECT @alias = STUFF((SELECT ',' + COALESCE(CAST(col2 AS nvarchar(100)), 'null') FROM columnametest FOR XML PATH('')),1,1,'');
SET @query = @query + ', col2 AS [' + @alias + ']'

SET @query = @query + ' FROM ColumNameTest'
PRINT @query
EXEC sp_executesql @query

动态构建的查询看起来像

The dynamically built query would look like

SELECT  col1 AS [1,3,null], col2 AS [2,null,6] FROM ColumNameTest

，结果是

and the result is

1,3,null   2,null,6
--------   --------
1          2
3          NULL
NULL       6

并非所有可以做到的。你为什么要那样做？想象一下，你有一系列没有任何标题的数据，例如： [1,0,0,4]每个元素代表什么？

另一种情况，如何将联系人存储在手机中，是否只存储电话号码或联系人姓名和电话号码？为什么是后者？你得到了答案。结论是不要这样做。

Not everything that can be done should be done. Why do you want to do that? Imagine you have a series of data without any heading, e.g. [1,0,0,4] what does each element represent?
Another scenario, how do you store your contacts in the phone, do you store just phone number or contact name and phone number? Why the latter? You got the answer. The conclusion is don't do it.

这篇关于Sql server：根据记录值返回列名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！