如何创建一个带有14000列的视图？ [英] How to create a view with 14000 Columns in it?

问题描述

我有两个表

1. 购买


2. b

1. Shop
2. Item.

和第三个表格，显示商店中商品的可用性以及该商店的商品成本。

and a third table which shows availability of Item in a shop with cost of the item in that shop.

Shop_Item_Mapping。

Shop_Item_Mapping.

Item的一些示例数据将是（1，Candy ），其中1是id，是项目的名称。

Some sample data of Item would be (1, Candy) where 1 is id and Candy is name of the Item.

(2 , Chocolate)
(3 , Chair)
(4 , Mobile)
(5 , Bulb)

Shop表的一些示例数据将

Some sample data of Shop table would be

(1 , Address Of Shop)
(2 , Address Of Shop)
(3 , Address of the shop)

现在我的映射表显示了哪个商品在哪个商店可用，

Now my mapping table shows me that which Item is available in which shop and at what cost.

Shop_Item_Mapping ( Shop_id , Item_Id , Cost of Item).

所以我的映射表有这些条目

So my mapping tables have these entries

SID , IID , Cost
(1  , 1,   5)
(1  , 2   ,10)
(1   ,4   ,2300)
(2   ,3   ,50)
(2  , 5   ,10)
(3  , 1  , 4)
(3  , 2  , 5 )
(3  , 4  , 2500 )
(3  , 5  , 12 )

现在我有一个查询，想要所有具有移动（id = 4）和巧克力（id = 2）的移动价格< 3000和巧克力价格小于7。

Now I have a query that I want all those shops which have both Mobile ( id = 4 ) and Chocolates(id = 2) with mobile price < 3000 and chocolate price less than 7.

我试图查看其中我将有这样的数据
Shop_ID，I1，I2，I3，I4 ，I5其中I1，I2，I3，I4，I5是项目的id，它们的值将是该商店中项目的成本。
所以我的观点是

I am trying to make a view where I will have data like this Shop_ID , I1 , I2 , I3 , I4 , I5 where I1 , I2 , I3 , I4 , I5 are the id of Items and value of these will be cost of the item in that shop. So my view would be

(1 , 5 , 10 , NULL , 2300 , NULL ) 
(2 , NULL , NULL , 50 , NULL , 10)
(3 , 4 , 5 , NULL , 2500 , 12 ).

我可以这样做，当我的项目少。但如果我在我的存储库中有超过15000个项目，我可以创建一个视图与这些许多列吗？

I am able to do so when my items are less. But IF I have more than 15000 Items in my repository, Can I create a view with these many columns ?

推荐答案

14,000列在视图？你在这里有一个严重的设计问题。但是，如果您想要去，请尝试此动态枢纽查询。它适用于您提供的有限数据：

Seriously? 14,000 columns in a view? You have a serious design issue here. However if you want to have a go, try this dynamic pivot query. It works with the limited data you have provided:

DECLARE @ColumnList VARCHAR (MAX)
DECLARE @SQL VARCHAR(MAX)


-- Create a list of distinct Item IDs which will become column headers
SELECT @ColumnList = COALESCE(@ColumnList + ', ','') + 'ItemID' + CAST(I.ItemID AS VARCHAR(12)) FROM (SELECT DISTINCT ItemID FROM Item) I


SET @SQL = '
SELECT
  ShopID, ' + @ColumnList + '
FROM
(  
  SELECT
    s.ShopID,
    ItemID = ''ItemID'' + Cast(i.ItemID as varchar(12)),
    sim.ItemCost
  FROM
      dbo.Shop_Item_Mapping AS sim
      JOIN dbo.Shop AS s ON sim.ShopID = s.ShopID
      JOIN dbo.Item AS i ON SIM.ItemID = i.ItemID
)  T
PIVOT
(
    MIN(ItemCost)
    FOR T.ItemID IN (' + @ColumnList + ')
) AS PVT'


exec (@SQL)

b $ b

根据更新的问题编辑字段名称。

Edited field names as per updated question.

这篇关于如何创建一个带有14000列的视图？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！