sql问题..如何动态添加列到SQL查询结果集？

I am writing a report to return details about an object ('files') in my database. My application lets users create their own flags for use against file objects. Flags basically consist of a name, then flag instances store a bit value to indicate whether it is set for the parent file object.

我正在撰写一份报告,以便在我的数据库中返回有关对象('文件')的详细信息。我的应用程序允许用户创建自己的标志以用于文件对象。标志基本上由一个名称组成,然后标志实例存储一个位值以指示它是否为父文件对象设置。

I want to write a query that returns one row per file in the database, where the first few columns in the result set contain the file details (id, name, size etc) and the remaining columns are the flag names, with bit values returned to indicate whether the flag is set for the given file row.

我想编写一个查询,在数据库中每个文件返回一行,其中结果集中的前几列包含文件详细信息(id,name,size等),其余列是标志名称,返回的是位值指示是否为给定文件行设置了标志。

Does that make sense? How do i go about writing the query?

那有意义吗?我该如何编写查询?

Thanks for any help.

谢谢你的帮助。

Edit: Clarification..

As part of this query, I need to run a sub query that returns the flags that have been created by the user (I do not know these at design time), then incorporate a check for each flag value in the main query to return details about the files.

作为此查询的一部分,我需要运行一个子查询,该子查询返回用户创建的标志(我在设计时不知道这些标志),然后在主查询中包含每个标志值的检查以返回详细信息关于文件。

Simplified schema as follows:

简化的架构如下:

File {Id, Name}

文件{Id,Name}

Flag {Id, Name}

标记{Id,Name}

FileFlags {FileId, FlagId} - a row in this table indicates that the flag is set for the file

FileFlags {FileId,FlagId} - 此表中的一行表示为该文件设置了标志

I need the query to return a result set with columns something like this:

我需要查询返回一个结果集,其中的列类似于:

FileId FileName Flag1Name Flag2Name .... FlagNName

FileId FileName Flag1Name Flag2Name .... FlagNName

4 个解决方案

#1

You could start by looking at the Pivot function available in SQL Server 2005+. With that and some string concatenation you should be able to assemble a query for any number of columns

您可以从查看SQL Server 2005+中提供的Pivot功能开始。使用它和一些字符串连接,您应该能够为任意数量的列组装查询

Based on your comment your select would look something like this:

根据您的评论,您的选择将如下所示:

        SELECT <non-pivoted column>,

            [first pivoted column] AS <column name>,

            [second pivoted column] AS <column name>, ...
    From Table 
    PIVOT (
...
FOR 
...
)

#2

Why not create a stored procedure with the required logic - querying user-specific flags, etc - and on that basis dynamically build a query string with the variables you need?

为什么不使用所需的逻辑创建存储过程 - 查询用户特定的标志等 - 并在此基础上动态构建包含所需变量的查询字符串?

You can then use EXECUTE to return the dynamic query set to the user. See http://msdn.microsoft.com/en-us/library/ms188332.aspx

然后,您可以使用EXECUTE将动态查询集返回给用户。请参阅http://msdn.microsoft.com/en-us/library/ms188332.aspx

#3

Sounds like perhaps you need a crosstab query, where you want to turn rows into columns. Are the flags stored in a separate table as a one to many relationship to the parent table?

听起来好像你需要一个交叉表查询,你想把行变成列。标志是否作为与父表的一对多关系存储在单独的表中?

#4

I think you want is an alias. Here is an example from http://www.sql-tutorial.net/SQL-Aliases.asp

我想你想要的是别名。以下是http://www.sql-tutorial.net/SQL-Aliases.asp中的示例

SELECT Employee, SUM(Hours) AS SumHoursPerEmployee
FROM EmployeeHours
GROUP BY Employee

I would really have to see your schema to help further.

我真的必须看到你的架构进一步帮助。

Edit Maybe you need nested SELECTs. http://sqlzoo.net/1a.htm

编辑也许你需要嵌套的SELECT。 http://sqlzoo.net/1a.htm

#1