sql - 具有动态(未知)列数的 T-Sql 查询

Question

我们有一个项目，我们应该为用户提供向各种表格添加自己的自定义列的可能性。

编辑:这是 2 个表，而不是一个。

**Products**
ProductId
Name
Price
Date
UserId

**ProductsCustomColumns**
ProductId
ColumnName
ColumnValue

编辑:请注意，动态列被记录为值，我们不知道这些的计数...它可以是 0 或 200 或任何值。

举个例子: 现在，当我们查询产品表时，我们想要显示所有预定义的列，然后是所有自定义列。显然，每个用户都可以拥有自己的列数，其中包含值和名称。

SELECT *, (and the custom columns) FROM Products WHERE UserId = 3 AND ProductId = 1

这里有两个问题:

1. 从性能的角度来看，这是一个很好的解决方案，还是有更好的方法来解决动态列要求？

2. 我如何创建一个查询，该查询可以从 ProductsCustomColumns 中读取给定 userId 和 productId 的所有记录并将记录附加为查询的列？

谢谢。

Answer 1

你需要写动态查询

DECLARE @COLUMNS VARCHAR(MAX)='', @QRY VARCHAR(MAX);

SELECT  @COLUMNS = @COLUMNS +COLUMN_NAME +',' FROM 
INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='Products'

SELECT @COLUMNS =SUBSTRING (@COLUMNS,1 ,LEN(@COLUMNS)-1)

SELECT @QRY ='SELECT '+@COLUMNS + ' FROM Products WHERE UserId = 3 AND ProductId = 1' 
EXEC (@QRY)

编辑:来自您的评论和编辑过的问题

我从你的问题中假设的模式

CREATE TABLE Products (
ProductId INT,
Name VARCHAR(250),
Price DECIMAL(18,2),
DateS DATETIME,
UserId INT)

INSERT INTO Products
SELECT 1,'Oil Product', 2000, GETDATE(), 3
UNION ALL
SELECT 2,'Amway', 600, GETDATE(), 2
UNION ALL
SELECT 3,'Thermal', 5000, GETDATE(), 1
UNION ALL
SELECT 4,'Oil Product', 500, GETDATE(), 4



CREATE TABLE ProductsCustomColumns
(
ProductId INT ,
ColumnName VARCHAR(200),
ColumnValue VARCHAR(15))

INSERT INTO ProductsCustomColumns
SELECT 1, 'Licence_No', '1545'
UNION ALL
SELECT 1, 'Location ', 'Atlanta'
UNION ALL
SELECT 2, 'Qty ', '5'
UNION ALL
SELECT 3, 'Gross', '80000'

现在你的动态代码放在这里

DECLARE  @COLUMN_PCC VARCHAR(MAX)='',  @PRODUCT_ID INT=1,@USER_ID INT=3, @QRY VARCHAR(MAX) ;

--preparing Custom Column Name List with comma ','
SELECT @COLUMN_PCC = @COLUMN_PCC+ [COLUMNNAME]  +',' FROM ProductsCustomColumns 
WHERE ProductId= @PRODUCT_ID
SELECT @COLUMN_PCC =SUBSTRING(@COLUMN_PCC,1,LEN(@COLUMN_PCC)-1)


--Preparing Dynamic Query
SELECT @QRY =' SELECT P.*, AV.* FROM Products P
INNER JOIN 
(
SELECT * FROM (
SELECT * FROM ProductsCustomColumns WHERE ProductId= '+CAST(@PRODUCT_ID AS VARCHAR(50))+'
)
AS A
PIVOT 
(
MAX (COLUMNVALUE)
FOR [COLUMNNAME] IN ('+@COLUMN_PCC +')
)AS PVT
)AS AV ON P.ProductId= AV.ProductId 
AND P.UserId='++CAST(@USER_ID AS VARCHAR(50))+'
'

EXEC ( @QRY)

结果是

 +-----------+-------------+---------+-------------------------+--------+-----------+------------+----------+
| ProductId |    Name     |  Price  |          DateS          | UserId | ProductId | Licence_No | Location |
+-----------+-------------+---------+-------------------------+--------+-----------+------------+----------+
|         1 | Oil Product | 2000.00 | 2016-12-09 18:06:24.090 |      3 |         1 |       1545 | Atlanta  |
+-----------+-------------+---------+-------------------------+--------+-----------+------------+----------+