SQL Server 2008中select子句中的动态列数 [英] Dynamic number of columns in select clause in SQL Server 2008

问题描述

我需要从表中动态获取列值.

I have the need to dynamically fetch column values from a table.

说我有一个包含列的表:

Say I have a table having columns:

Name,
Date,
Col1,
Col2,
Col3,
...
col10

现在，根据某些条件，我想从上面保留Name的表中从col1到col10提取不同的列，日期列始终在select子句中.例如如下.

Now, depending on some condition I want to fetch different columns from col1 to col10 from above table keeping Name, date columns always in select clause. e.g. is as below.

columns in output:  (1) Name (2) date (3) col1 + col2 + col3  (4) col4 + col5

即列数和列本身(colN)都不同.

i.e. number of columns and columns itself (colN) both varies.

我该如何实现?另外，固定列直接在select子句中指定，例如.

How can I achieve that? Also, fixed columns are specified directly in select clause like.

select Name, Date  ... from Table1

但是，为了获取每一行的剩余列值，我正在使用表值函数.那么，在该功能中我如何获得价值?我可以创建一个动态SQL字符串，但无法在函数中执行它，所以这是我面临的局限性.

But, to get remaining column values for each row I am using table-valued function. So, in that function how can I get value? I can make a dynamic SQL string but cannot execute it in a function, so this is the limitation I am facing.

推荐答案

最后通过生成一个动态查询字符串然后执行它来实现这一点.

Finally achieved this by generating a dynamic query string then executing it.

要生成动态字符串，我首先使用"with"获取固定的列，然后使用每行运行函数.如下所示.

To generate dynamic string I first take the fixed columns using "with" and then with each row running function. Something as below.

set @str = ';with fn as (select ... fixed-columns from table) select x.*'

while (condition)
begin
   set @cols = @cols + function_to_generate_dynamic_columns(@params)
   set @i=@i+1
end

set @str = @str + @cols + ' from fn x '

EXECUTE(@str)

这篇关于SQL Server 2008中select子句中的动态列数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！