购买项目

Question

非规范化的数据我有如下表：

BoughtItems 

UserID ItemID StoreID Quantity Price 
1  1  1  1   1 
1  4  1  2   3 
1  5  2  1   3 

假设，这将是为BoughtItems表中的每个用户再没有其他五个项目。

我想有一些STOREID所有的客户，并已购买的，在这种格式...为STOREID = 1的所有项目的结果将是：

UserID Item1ID Item2ID Item3ID Item4ID Item5ID 
1  1  4  [empty] [empty] [empty] 

Answer 1

DECLARE @StoreID INT; 

SET @StoreID = 1; 

;WITH x AS 
(
    SELECT UserID, ItemID, 
    rn = ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY ItemID) 
    FROM dbo.BoughtItems 
    WHERE StoreID = @StoreID 
) 
SELECT UserID, 
    Item1ID = MAX(CASE WHEN rn = 1 THEN ItemID END), 
    Item2ID = MAX(CASE WHEN rn = 2 THEN ItemID END), 
    Item3ID = MAX(CASE WHEN rn = 3 THEN ItemID END), 
    Item4ID = MAX(CASE WHEN rn = 4 THEN ItemID END), 
    Item5ID = MAX(CASE WHEN rn = 5 THEN ItemID END) 
FROM x WHERE rn <= 5 
GROUP BY UserID;