SQL transforming one row to columns with unknown number of distinct values

I have a following database

id  item_id  date        system_code  value
#1  0001     01-01-2019  0999         10
#2  0001     02-01-2019  0888         15
#3  0001     01-01-2019  0999         10
#4  0002     02-01-2019  0777         20
#5  0002     03-01-2019  0777         35

And the result I want to achieve would look like so, with values for system_code columns being sum of “value”

  item_id  date        0999  0888  0777
  0001     01-01-2019  20        
  0001     02-01-2019        15
  0002     02-01-2019              20
  0002     03-01-2019              35

My problem is that there is over 1000 distinct values for system_code so I can’t type them by hand. Database has nearly billion entries so anything that would calculate quickly would be perfect.

Answer

Just in case, here is a dynamic PIVOT

Example

Declare @SQL varchar(max) = stuff((Select distinct  ',' + QUOTENAME([system_code]) 
                                     From YourTable
                                     Order By 1 Desc
                                    For XML Path('') )
                                   ,1,1,'') 

Set @SQL = '
Select *
 From  ( Select item_id,date,system_code,value from YourTable ) src
 Pivot ( sum(value) for system_Code in ( '+ @SQL + ') ) pvt
'

--Print @SQL
Exec(@SQL)

Returns

Note:

I believe the max number of columns is 1,024 (wide table 30,000).

Personally, I would hate to consume more than 50 columns.

EDIT:

If you have two many columns, you can filter the list by adding a WHERE in the first query and perhaps run it twice (or more). The results would have the same number of rows, just different column sets.

Example

Declare @SQL varchar(max) = stuff((Select distinct  ',' + QUOTENAME([system_code]) 
                                   From #YourTable
                                   Where [system_code] >'0500' -- or any appropriate filter
                                   Order By 1 desc
                                   For XML Path('') )
                                  ,1,1,'')