Making columns from one column's data in SQL Server

Question

I have a SQL Server table like this

ID    amount        type
1         10        material
1          5        spare parts
1          5        material

I need to make a query and get outputs like this

ID        material        spare parts
1            15                     5

But I have too many IDS and types, so I need to add items dynamically whatever their count.

Answer 1

You are looking for a dynamic pivot. Basically this works selecting the list of types from the table, then building a query from that information. You can then execute the query with sp_executesql.

For your table structure:

declare @sql nvarchar(max);

select @sql = string_agg(
    'sum(case when type = ''' + type + ''' then amount else 0 end) [' + type + ']', 
    ', ') 
from (select distinct type from mytable) t;

set @sql = N'select id, ' + @sql + ' from mytable group by id';
select @sql;                 -- debug
-- exec sp_executesql @sql;  -- execute for real

For your sample data, this generates the following query (I added line breaks added for readability):

select 
    id,
    sum(case when type = 'material' then amount else 0 end) [material], 
    sum(case when type = 'spare parts' then amount else 0 end) [spare parts] 
from mytable
group by id

After execution, you get result:

id | material | spare parts
-: | -------: | ----------:
 1 |       15 |           5

Demo on DB Fiddle