SQL - Crosstab Queries

https://www.simple-talk.com/sql/t-sql-programming/creating-cross-tab-queries-and-pivot-tables-in-sql/

Use this SP for Dynamic Columns

OR use this technique to build the list of columns first and remove nulls
https://stackoverflow.com/questions/51278149/how-to-remove-null-values-from-an-sql-pivot
-------------------------------------------------------
Declare @Locs  varchar(max)
Declare @Locs2  varchar(max)
Declare @Pivot varchar(max)


select @Locs = AllComm from (
SELECT distinct
       (STUFF((SELECT distinct CAST(', [' + [Location Code] + ']' AS VARCHAR(MAX))
         FROM [BI-ItemQty]
         WHERE [Location Code] > ''
         FOR XML PATH ('')), 1, 2, '')) AS AllComm
FROM [BI-ItemQty]) as A

select @Locs2 = AllComm from (
SELECT distinct
       (STUFF((SELECT distinct CAST(', ISNULL([' + [Location Code] + '],0)  ['+[Location Code] + ']'  AS VARCHAR(MAX))
         FROM [BI-ItemQty]
         WHERE [Location Code] > ''
         FOR XML PATH ('')), 1, 2, '')) AS AllComm
FROM [BI-ItemQty]) as B


Set @Pivot =
N'select [Item No_]
      ,[Posting Date]
      ,[Document Type]
      ,[DocTypeDesc]
      ,[Entry Type]
      ,[EntryTypeDesc]
      ,[Receipts]
      ,[Sales]
      ,[Rtns]
      ,[Adjustments]
      ,[Assembly],'+@Locs2+' from(SELECT *
  FROM [LS13].[dbo].[BI-ItemQty]) ps
  PIVOT
   ( SUM (Quantity)
     FOR [Location Code] in ('+@Locs+')
   ) AS pvt'
print @Pivot
EXECUTE (@Pivot)

----------------------------------------------------------------------------------------------------

You can use this technique for fixed columns

https://www.mssqltips.com/sqlservertip/1019/crosstab-queries-using-pivot-in-sql-server/

SELECT SalesPerson, [Oranges] AS Oranges, [Pickles] AS Pickles
FROM 
(SELECT SalesPerson, Product, SalesAmount
FROM ProductSales ) ps
PIVOT
(
SUM (SalesAmount)
FOR Product IN
( [Oranges], [Pickles])