Here is an example of a working pivot based loosely on your sample data
declare @itemMaster table (Item varchar(3), amount decimal(15,2));
insert into @itemMaster (Item, amount) values
('A1',100),('A2',200),('B1',1000),('C1',10000);
select
*
from
(select Item,amount from @ItemMaster) p
pivot (
sum([amount]) for Item in (A1, A2, B1, C1)
) pivoted
This CP article gives some guidance
Simple Way To Use Pivot In SQL Query[
^]
From the OP comments below I think they might just need those items in a character separated list rather than a pivot on values. Here are some ways to achieve that
1. From SQL Server 2017 you can use STRING_AGG
SELECT STRING_AGG(item, '|') as columnlist from @itemMaster
2. In earlier versions you can use FOR XML e.g.
SELECT TOP 1
STUFF((
SELECT DISTINCT '|' + Item
FROM @itemMaster
FOR XML PATH('')
), 1, 1, ''
) as columnlist
FROM @itemMaster
3. For even earlier versions you can use a Common Table expression e.g.
DECLARE @listStr VARCHAR(MAX) = null
;WITH cteitems AS
(
SELECT DISTINCT Item from @itemMaster
)
SELECT @listStr = COALESCE(@listStr+'|' ,'') + Item
FROM @itemMaster
select @listStr
All of the above give the output
A1|A2|B1|C1
without knowing what values exist