根据SQL Server中另一个表的行动态选择列名 [英] Select column name dynamically based on rows from another table in SQL server

问题描述

您好，



我想在选择查询中显示我的动态列值。

我的列名存储在另一个表中。< br $>

Hello,

I want to display my dynamic column value in select query.
my column name stored in another table.

select ColumnName from LCompanySpecificColumns CSC where CSC.Label='Duration' and CSC.CompanyCode = 'DE' and CSC.TableName = 'LProducts' and CSC.SelecterType = 'Kias-Soc'

此查询结果是加入

this query Result is with join

AttributeC02

AttributeC02

AttributeC02

AttributeC02

<pre>AttributeC02

是我在LProducts表中的列名。

我想得到结果： -

AttributeC02 //列名

C12

C223



请帮帮我。



在此先感谢。

Ankit Agarwal

软件工程师



我的尝试：

is my column name in LProducts Table.
I want to result:-
AttributeC02 // Column Name
C12
C223

Please help me.

Thanks in Advance.
Ankit Agarwal
Software Engineer

What I have tried:

select LP.ProductCode as ProductId,LP.Name as PRODUCT_DESCRIPTION,PC.Name as PRODUCT_ID_CATEGORY,PS.Name as SOURCE_SYSTEM,(select ColumnName from LCompanySpecificColumns CSC where CSC.Label='Duration' and CSC.CompanyCode = 'DE' and CSC.TableName = 'LProducts' and CSC.SelecterType = 'Kias-Soc') as ESTIMATED_CONTRACT_DURATION_ID from LProducts as LP 
inner join RSysCat as SC on LP.SysCatId=SC.Id
inner join RProductCategories PC on SC.CategoryId=PC.Id
inner join RProductSystems PS on SC.SystemId=PS.Id

推荐答案

唯一的方法就是使用将命令构建为字符串，并使用EXEC运行结果：表和列名解析在SQL命令执行过程的早期进行，并且在执行任何实际查询之前已被替换。



EXECUTE（Transact-SQL） | Microsoft Docs [ ^ ]

The only way to do that is use build your command into a string, and use EXEC to run the result: table and column name parsing is conducted early in the SQL command execution process and have been replaced before any of the actual query is executed.

EXECUTE (Transact-SQL) | Microsoft Docs[^]

SELECT ID,Col_Names INTO #Temp1 FROM 
                                     (VALUES(12,'EmpId'),
									        (21,'EmpName'),
											(23,'Location'))AS Temp1(ID,Col_Names);

SELECT EmpId,EmpName,Location  into #Temp2 FROM 
                                               (VALUES(1246,'Santosh kumar','HYD'),
											          (1215,'Harish Kumar','Channai'),
													  (1234,'Sai kiaran','Medak'),
													  (1246,'Satish','SangaReddy'))AS Temp2(EmpId,EmpName,Location);

DECLARE @query NVARCHAR(MAX);
SET @query=N'select '+(select Col_Names FROM #Temp1 WHERE ID=21)+' from #Temp2';

EXEC Sp_EXECUTESQL @query;

这篇关于根据SQL Server中另一个表的行动态选择列名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！