how to select multiple column name dynamically In SQL Server-CodePudding

How to Select a Column's Value Dynamically from a Table, where columns are not fixed?

I have a table name EMP which contains a numbers of dynamic columns.

id     Items    Col1      Col2   Col3   Col4  ... .... ... Col20   -----> Columns 

 1       ABC    20         40    100    32    ..  ..  .    200     -----> Values

Now I have a Dynamic Formula like

Round(Col1 Col4 Col20,2) 100

For Solving this formula I have to fetch the particular columns's value, which are associated to that columns and finally execute the query according to the formula.

I can Apply 2 methods

                                    First Method

select col1,col4,col20 from EMP

but it will not work because I do not know the columns's name, even I get extract the required columns form the formula, but after getting the columns name. how can i select them dynamically from the EMP table?

                            ****Second Method****

declare @holder int,@holder2 int.....

SELECT @holder=col1,@holder=col2..... FROM emp

It will also not work, because how can I Declare the a numbers of Dynamic variable for every column?

How can I do it? Please Help

CodePudding user response：

This is a very dubious requirement. Tables shouldn't have dynamic columns.

You can do something horrible with XML though (DB Fiddle)

 SELECT t.*, CA2.sum
 FROM YourTable t
 CROSS APPLY (SELECT t.* FOR XML PATH('x'), TYPE) CA(X)
 CROSS APPLY (SELECT SUM(CASE WHEN n.value('local-name(.)', 'sysname') LIKE 'Col%' THEN n.value('.','int') END) FROM  CA.X.nodes('x/*') n(n)) CA2(sum)

Above XML was hacked together and could doubtless be improved upon!

CodePudding user response：

You should only use Dynamic SQL for this purpose. like this :

declare @columns varchar(max) = 'Col1 , Col2 , Col3' ;    
declare @sql varchar(mx) = 'SELECT '  @columns  'FROM emp' ;