在sql查询中添加N个动态列
我有一个称为datarecords的表，其中包含7个固定列，这些列在选择查询中始终是必需的。 用户可以添加他们想要的任意多个自定义列。 我将这些信息存储在一个名为datacolumn的表中，这些值存储在另一个名为datavalue表中。
现在我想创建一个查询，从datarecord带出7个固定列，然后添加自定义列并从这些表中获取数据值，因为每个数据记录在数据值表中都有相应的值。
您可以尝试将行中的自定义属性转换为列，但是您会发现，即使在Microsoft SQL Server中支持PIVOT，您也需要在编写查询之前了解这些属性，并且查询代码需要指定全部属性。 SQL中没有办法要求所有的自定义属性根据需要神奇地填充尽可能多的列。
您可以通过逐行读取自定义属性来检索任意数量的自定义属性，因为它们存储在数据库中。 然后编写应用程序代码来遍历结果。 如果你愿意，你可以编写一个类将自定义属性的多行映射到应用程序中的一个对象的字段中。
使用SQL查询非关系数据很尴尬和不雅。 这是因为SQL旨在假定每个相同类型的逻辑实体具有固定数量的列，并且在编写查询之前知道列。 如果您的实体具有可变属性，则根据定义，它不能存储为关系。
许多人试图使用您正在使用的设计来扩展它，但他们发现很难管理，并且不能很好地扩展。 这种设计通常称为实体属性值模型或键值对。 有关EAV设计的陷阱的更多详细信息，请参阅我的书SQL反模式。
如果你需要支持自定义属性，这里有几个选择：
将所有自定义属性一起存储在BLOB中，并使用一些内部结构来分隔字段名称和值（序列化LOB）。 您可以选择创建倒排索引以帮助您查找给定字段具有给定值的行（请参阅FriendFeed如何使用MySQL）。
将面向文档的数据库（如MongoDB或Solr）用于动态数据。
当用户需要自定义属性时，使用ALTER TABLE将常规列添加到表中。 这意味着您需要为所有用户强制执行相同的一组自定义属性，或者存储所有用户的自定义属性，并希望表不会太宽（Single Table Inheritance），或者为每个用户创建一个单独的表用于所有列（混凝土表继承）或仅用于自定义列（类表继承）。
编辑：请参阅注意底部的更多细节。
我面临同样的问题，并且我找到了一个缓慢的解决方案。 也许别人有加快我的研究结果的解决方案。 在我的代码中，我有一个包含三列的表格：Col1，Col2，Col3。 Col1是我的记录ID。 Col2是我的动态列的名称。 Col3是该列的值。 所以如果我想用ID 1，两列2和3以及这些列的值4和5来表示记录，我会得到以下结果：
Col1, Col2, Col3
1, 2, 4
1, 3, 5
然后，我们转到第2列并选择MAX（或MIN或AVG，无关紧要，因为col2和col3组合是唯一的）col3在数据透视中。 为了完成可变数量列的数据透视表，我们使用动态SQL生成来生成我们的SQL。 这适用于小输入数据（我相信动态SQL的FROM子句内的派生表）。 一旦数据集变大，平均功能开始需要很长时间才能执行。 很长一段时间。 它看起来像大约1000行开始，所以也许有一个提示或其他方法可以缩短。
值得注意的是，由于Col2和Col3的映射值为1：1，我还尝试动态生成如下所示的SELECT语句：
SELECT Col1,
CASE WHEN Col2 = '4' THEN Col3 END [4],
CASE WHEN Col2 = '5' THEN Col3 END [5],
CASE WHEN Col2 = '6' THEN Col3 END [6], -- ... these were dyanmically generated
FROM #example
GROUP BY Col1
这对我的数据集来说是一样慢。 你的里程可能会有所不同。 这里是一个完整的例子，说明如何为SQL Server编写这个工程（2005+应该运行这个）。
--DROP TABLE #example
CREATE TABLE #example
(
Col1 INT,
Col2 INT,
Col3 INT
)
INSERT INTO #example VALUES (2,4,10)
INSERT INTO #example VALUES (2,5,20)
INSERT INTO #example VALUES (2,6,30)
INSERT INTO #example VALUES (2,7,40)
INSERT INTO #example VALUES (2,8,50)
INSERT INTO #example VALUES (3,4,11)
INSERT INTO #example VALUES (3,5,22)
INSERT INTO #example VALUES (3,6,33)
INSERT INTO #example VALUES (3,7,44)
INSERT INTO #example VALUES (3,8,55)
DECLARE @columns VARCHAR(100)
SET @columns = ''
SELECT @columns = @columns + '[' + CAST(Col2 AS VARCHAR(10)) + '],'
FROM (SELECT DISTINCT Col2 FROM #Example) a
SELECT @columns = SUBSTRING(@columns, 0, LEN(@columns) )
DECLARE @dsql NVARCHAR(MAX)
SET @dsql = '
select Col1, ' + @columns + '
from
(select Col1, Col2, Col3 FROM #example e) a
PIVOT
(
MAX(Col3)
FOR Col2 IN (' + @columns + ')
) p'
print @dsql
EXEC sp_executesql @dsql
编辑：由于我这样做的独特情况，我设法使用两个表（一个具有实体，另一个具有属性 - 值对）加快速度，并在属性值上创建聚集索引其中包括所有列（ID，属性，值）。 如果您需要快速插入，大量列，许多数据行等，我建议您以另一种方式解决这种方法。我对数据的大小和增长率有一些已知的确定性，myy解决方案适合我的范围。
还有很多其他解决方案更适合解决这个问题。 例如，如果您需要快速插入和单个记录读取（或慢速读取无关紧要），则应考虑将XML字符串打包到字段中，并在数据库使用者中进行序列化/反序列化。 如果您需要超快速写入，超快速读取和数据列很少添加，那么您可以考虑更改表格。 在大多数实践中这是一个不好的解决方案，但可能适合一些问题。 如果您的列有足够频繁的更改，但是您还需要快速读取和写入不是问题，那么我的解决方案可能适用于某些数据集大小。
链接地址: http://www.djcxy.com/p/68627.html