SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

SET ANSI_WARNINGS OFF

--Example created from StackOverflow post 
--https://stackoverflow.com/questions/77915889/creating-a-pivot-sql-table-from-two-row-values-into-two-columns-dynamic-amount#77915889
CREATE TABLE CustomerSales 
(
    custname	VARCHAR(512),
    week#	INT,
    sales	decimal(10,2),
    orders	INT
);

INSERT INTO CustomerSales (custname, week#, sales, orders) VALUES ('CustAAA', -1, 974697.41, '62013');
INSERT INTO CustomerSales (custname, week#, sales, orders) VALUES ('CustAAA', 1, 10.01, '5');
INSERT INTO CustomerSales (custname, week#, sales, orders) VALUES ('CustAAA', 2, 10, '2');
INSERT INTO CustomerSales (custname, week#, sales, orders) VALUES ('CustAAA', 2, 372.95, '11');
INSERT INTO CustomerSales (custname, week#, sales, orders) VALUES ('CustAAA', 3, 70.86, '13');
INSERT INTO CustomerSales (custname, week#, sales, orders) VALUES ('CustAAA', 3, 0, '3');
INSERT INTO CustomerSales (custname, week#, sales, orders) VALUES ('CustAAA', 4, 8.08, '2');
INSERT INTO CustomerSales (custname, week#, sales, orders) VALUES ('CustAAA', 5, 20, '6');
INSERT INTO CustomerSales (custname, week#, sales, orders) VALUES ('CustAAA', 48, 0, '38');
INSERT INTO CustomerSales (custname, week#, sales, orders) VALUES ('CustAAA', 49, 84.27, '2');
INSERT INTO CustomerSales (custname, week#, sales, orders) VALUES ('CustXYZ', -1, 12.12, '1');
INSERT INTO CustomerSales (custname, week#, sales, orders) VALUES ('CustXYZ', 1, 22.59, '1');
INSERT INTO CustomerSales (custname, week#, sales, orders) VALUES ('CustXYZ', 4, 117.9, '1');
INSERT INTO CustomerSales (custname, week#, sales, orders) VALUES ('CustXYZ', 48, 19.3, '1');

Select * from CustomerSales

--Bart McEndree JAN 31, 2024
--Dynamic cross tab solution based on https://weblogs.sqlteam.com/jeffs/2005/05/02/4842/ by Jeff Smith and Rob Volk

Declare @Select varchar(800) ='SELECT CustName, week#, sales, orders FROM CustomerSales';
    Declare @PivotCol varchar(100) = 'week#';
    Declare @Summaries varchar(100) = 'Sum(CASE sales WHEN NULL THEN 0 ELSE sales END)[ Sales],SUM(orders)[ Orders]';
    Declare  @GroupBy varchar(100)  = 'CustName';
    Declare  @OrderBy varchar(100) = 'CustName';
    
	declare @Vals varchar(8000);
	set @Vals = '';
   
	create table #temp (Pivot1 int)

insert into #temp
	exec ('SELECT distinct convert(varchar(100),' + @PivotCol + ') as PIVOT1 FROM (' + @Select +') A')
      
	Select @Vals = @Vals + ', ' +
	   replace(replace(@Summaries,'(','(CASE WHEN ' + @PivotCol + '=''' + 
	             CONVERT(varchar(10), Pivot1) + ''' THEN '),')[', ' END) as [Week ' + CONVERT(varchar(10), Pivot1)   )
    FROM #Temp
	ORDER BY Pivot1

drop table #Temp

exec ('SELECT ' + @GroupBY + @vals + ' FROM (' + @Select + ') A GROUP BY  ' + @GroupBy + ' ORDER BY ' + @OrderBy )

SET ANSI_WARNINGS ON

custname	week#	sales	orders
CustAAA	-1	974697.41	62013
CustAAA	1	10.01	5
CustAAA	2	10.00	2
CustAAA	2	372.95	11
CustAAA	3	70.86	13
CustAAA	3	0.00	3
CustAAA	4	8.08	2
CustAAA	5	20.00	6
CustAAA	48	0.00	38
CustAAA	49	84.27	2
CustXYZ	-1	12.12	1
CustXYZ	1	22.59	1
CustXYZ	4	117.90	1
CustXYZ	48	19.30	1

CustName	Week -1 Sales	Week -1 Orders	Week 1 Sales	Week 1 Orders	Week 2 Sales	Week 2 Orders	Week 3 Sales	Week 3 Orders	Week 4 Sales	Week 4 Orders	Week 5 Sales	Week 5 Orders	Week 48 Sales	Week 48 Orders	Week 49 Sales	Week 49 Orders
CustAAA	974697.41	62013	10.01	5	382.95	13	70.86	16	8.08	2	20.00	6	0.00	38	84.27	2
CustXYZ	12.12	1	22.59	1	null	null	null	null	117.90	1	null	null	19.30	1	null	null