Dynamically Choose Column in SQL Query

Dynamically choose column in SQL query

Try this:

DECLARE @var1 VARCHAR(20)
DECLARE @sql VARCHAR(255)

SET @var1 = 'Code'
SET @sql = 'select ' + QUOTENAME(@var1) + ' from [VoucherType] where [DeletedBy] is null and [AutoID] = 1'

EXEC sp_executesql @sql

You'll have to compose a dynamic query, and execute using sp_executesql

To add more on the 'dynamic' side of things, use stored procedures. See here for an example:

http://www.marten-online.com/database/execute-dynamic-sql-in-mssql.html

That is... if you are using Microsoft SQL SERVER

Dynamically selecting the column to select from the row itself in SQL

You just need a CASE expression...

SELECT
  id,
  SelectedP,
  CASE SelectedP
    WHEN 'P1' THEN P1
    WHEN 'P2' THEN P2
    WHEN 'P3' THEN P3
    WHEN 'P4' THEN P4
    WHEN 'P5' THEN P5
  END
    AS SelectedPValue
FROM
  yourTable

This will return NULL for anything not mentioned in the CASE expression.

EDIT:

An option with just a little less typing...

SELECT
  id, SelectedP, val  
FROM   
  yourTable AS pvt
UNPIVOT  
(
  val FOR P IN   
  (
    P1,
    P2,
    P3,
    P4,
    P5
  )
)
  AS unpvt
WHERE
  SelectedP = P

NOTE: If the value of SelectedP doesn't exist in the UNPIVOT, then the row will not appear at all (unlike the CASE expression which will return a NULL)

Demo: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=b693738aac0b594cf37410ee5cb15cf5

EDIT 2:

I don't know if this will perform much worse than the 2nd option, but this preserves the NULL behaviour.

(The preferred option is still to fix your data-structure.)

SELECT
  id, SelectedP, MAX(CASE WHEN SelectedP = P THEN val END) AS val
FROM   
   yourTable AS pvt
UNPIVOT  
(
  val FOR P IN   
  (
    P1,
    P2,
    P3,
    P4,
    P5
  )
)
  AS unpvt
GROUP BY
  id, SelectedP

Demo : https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=f3f64d2fb6e11fd24d1addbe1e50f020

SQL Server : dynamically select column based on another select column

You need to create a dynamic unpivot statement and execute it.

Try not to get confused about what is the static and what the dynamic parts.

DECLARE @sql nvarchar(max) = N'
SELECT v.*
FROM (
    SELECT TOP (1) *
    FROM ' + QUOTENAME(@tablename) + N'
    ORDER BY CHECKSUM(NEWID())
) t
CROSS APPLY (VALUES
' +
(
    SELECT STRING_AGG(CAST(
N'(' + QUOTENAME(c.name, '''') + N', CAST(t.' + QUOTENAME(c.name) + N' AS sql_variant))'
        AS nvarchar(max)), N',
') WITHIN GROUP (ORDER BY c.name ASC)
    FROM sys.columns c
    WHERE c.object_id = OBJECT_ID(@tablename)
) +
N'
) AS v(columnName, columnValue);
';

PRINT @sql;  -- for testing

EXEC sp_executesql @sql;

---

If you are unfortunate enough to still be on a version not supporting STRING_AGG, you can use FOR XML instead for that part:

-------
CROSS APPLY (VALUES
' +
STUFF(
(
    SELECT N',
(' + QUOTENAME(c.name, '''') + N', CAST(t.' + QUOTENAME(c.name) + N' AS sql_variant))'
    FROM sys.columns c
    WHERE c.object_id = OBJECT_ID(@tablename)
    ORDER BY c.name ASC
    FOR XML PATH(''), TYPE
).value('text()[1]', 'nvarchar(max)'), 1, 2, '') +
N'
) AS v(columnName, columnValue);
';
--------

how to select multiple column name dynamically In SQL Server

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' ;

SQL Server select query dynamic column output

Provided the WEEK numbers and Order numbers are consistent, it is a small matter to maintain the column sequence.

You may notice I used #forecast and #article because I did not know your actual table names.

Example

Declare @SQL varchar(max) = '
Select *
 From (
        Select A.ArticleID
              ,D.Description 
              ,B.*
         From  #forecast  A
         Join  #article   D on A.ArticleID=D.ArticleID
         Cross Apply (values (''Week''+left(Week,4),Amount) ) B(Item,Value)
      ) A
 Pivot (max([Value]) 
        For [Item] in (' + Stuff((Select ','+QuoteName('Week'+left(Week,4)) 
                                   From (Select Distinct top 100 [Order],Week From #forecast Order by [Order]  ) A  
                                   For XML Path('')),1,1,'') + ') ) p'
Exec(@SQL);
--Print @SQL

Returns

ArticleID   Description Week51  Week52  Week1   Week2   Week3
1           Test        0       150     0       200     0

SQL Server Is there a way to select columns from a given list

You would construct the query and use dynamic SQL. Assuming the "list" is really a comma-delimited list:

declare @sql nvarchar(max);

set @sql = 'select [cols] from tablename';

set @sql = replace(@sql, '[cols]', @cols);

exec sp_executesql @sql;

EDIT:

To get the columns from a table, you can use the trick:

declare @cols varchar(max);

select @cols = ', ' + columnname
from @table t;

You then have to remove the first comma (using stuff()) in the previous code.

---

Related Topics