Click here to Skip to main content
15,868,016 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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
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
Posted
Updated 10-Jan-18 22:22pm

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[^]
 
Share this answer
 
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;
 
Share this answer
 
Comments
Member 13308004 13-Sep-18 4:47am    
Hello, I took below error when I need #Temp2 column. Could you help me please?
The multi-part identifier "#Temp2.EmpId" could not be bound.

SELECT ID,Col_Names INTO #Temp1 FROM
(VALUES(12,'EmpId'),
(1215,'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 IN (#Temp2.EmpId))+' from #Temp2';

print @query

EXEC Sp_EXECUTESQL @query;

drop table #Temp1
drop table #Temp2
Santosh kumar Pithani 13-Sep-18 5:16am    
Can i know what is this '#Temp2.EmpId)';what you expecting from me?
Member 13308004 13-Sep-18 5:47am    
Hello,
I need to use EmpId value in #Temp2 table at the place of defined dynamic column names.
When I write the #Temp2 table's column in that dynamic area it gives error.
"The multi-part identifier "#Temp2.EmpId" could not be bound."
As I understand, it refer as text not understand #Temp2.EmpId.

Thanks,
Santosh kumar Pithani 13-Sep-18 6:07am    
if your getting columns name from other table(#temp1) then give its id in filter condition like 'SET @query=N'select '+(select Col_Names FROM #Temp1 WHERE ID=12)+' from #Temp2' '.Here ID(12) belongs to column(EmpId).

If you want to give direct then use
Declare @column_name varchar(500)='EmpId'
SET @query=N'select '+@column_name+' from #Temp2;'
Member 13308004 13-Sep-18 6:04am    
SELECT ID,Col_Names INTO #Temp1 FROM
(VALUES(12,'EmpId'),
(999,'EmpName'),
(15,'Location'))AS Temp1(ID,Col_Names);

SELECT EmpId,EmpName,Location into #Temp2 FROM
(VALUES(123,'Ali Veli','www'),
(999,'Ugur','rrr'),
(124,'Aydin','bbb'),
(126,'Ercan','eeee'))AS Temp2(EmpId,EmpName,Location);

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

print @query

EXEC Sp_EXECUTESQL @query;

drop table #Temp1
drop table #Temp2

But I need like below:
SELECT ID,Col_Names INTO #Temp1 FROM
(VALUES(12,'EmpId'),
(999,'EmpName'),
(15,'Location'))AS Temp1(ID,Col_Names);

SELECT EmpId,EmpName,Location into #Temp2 FROM
(VALUES(123,'Ali Veli','www'),
(999,'Ugur','rrr'),
(124,'Aydin','bbb'),
(126,'Ercan','eeee'))AS Temp2(EmpId,EmpName,Location);

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

print @query

EXEC Sp_EXECUTESQL @query;

drop table #Temp1
drop table #Temp2

It gives error:
Msg 4104, Level 16, State 1, Line 14
The multi-part identifier "#Temp2.EmpId" could not be bound.

I need to use #Temp2 column in parentheses.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900