Convert rows to columns using pivot-CodePudding

I try to convert this procedure to PIVOT, but I can't. Does anyone have a solution to help? I have a table has ItemID, StoreID,Stock I want to convert it to ItemID, Store1,Store2,Store3...,Stock sum the stock according to itemID and StoreID then inserts the result as a row. Many thanks

 CREATE table #test222
    ([Id] int,[ItemID] INT, [storeid] int, [stock] decimal(18,2)
);
    
INSERT INTO #test222
    ([Id],[ItemID], [storeid], [stock])
VALUES
    (1, 1, 3,10),
    (2, 1,1, 20),
    (3, 1,1, 30),
    (4, 2,1, 40),
    (5, 2,2,50),
    (6, 2,2,60),
    (7, 3,2,70),
    (8, 4,2,80),
    (9, 4,2,90),
    (10, 5,2,100);

select * from #test222;

select ItemID, store1,store2,storeid3,storeid4,storeid5,storeid6,storeid7,storeid8,storeid9,storeid10 stock 
from
(
  select  ItemID, storeid, stock 
  from #test222
) d
pivot
(
  max(stock)
  for storeid in (1,2,3,4,5,6,7,8,9,10)
) piv;

Give error:

Msg 102 Level 15 State 1 Line 9 Incorrect syntax near '1'.

CodePudding user response：

Here is a simple PIVOT. Just remember to "feed" your pivot with just only the required columns

Example

Select *
 From  (
        Select ItemID
              ,Col    = 'store' left(storeid,10)
              ,val    = stock
         From  #test222
       ) src
 Pivot ( max(val) for Col in ( store1,store2,storeid3,storeid4,storeid5,storeid6,storeid7,storeid8,storeid9,storeid10  ) ) src

Results