Dynamic Pivot with group by, dynamic column name and dynamic column list

I need something like a pivot with group by and dynamic column names?

I have a query which gives me data like this:

For this question, we can assume that the data is in #Test

Now, I need to get a structure like this:

Code for my example:

create table Test
(
  name varchar(20) not null
, Type1 varchar(20) not null
, Type2 varchar(20) not null
, Value varchar(50) null
)

insert into dbo.Test (name, Type1, Type2, Value) values ('name1', 'Short', 'A', 'loreipsum1')
insert into dbo.Test (name, Type1, Type2, Value) values ('name1', 'Short', 'B', 'loreipsum2')
insert into dbo.Test (name, Type1, Type2, Value) values ('name1', 'Long', 'A', 'loreipsum3')
insert into dbo.Test (name, Type1, Type2, Value) values ('name1', 'Long', 'B', 'loreipsum4')
insert into dbo.Test (name, Type1, Type2, Value) values ('name2', 'Short', 'A', 'loreipsum5')
insert into dbo.Test (name, Type1, Type2, Value) values ('name2', 'Short', 'B', 'loreipsum6')
insert into dbo.Test (name, Type1, Type2, Value) values ('name2', 'Long', 'A', 'loreipsum7')
insert into dbo.Test (name, Type1, Type2, Value) values ('name2', 'Long', 'B', 'loreipsum8')

select * into #Data from dbo.Test;

select x.*
into #Typ1
from (select 'Short' Typ
      union
      select 'Long' Typ
     ) x;

select x.*
into #Typ2
from (select 'A' Typ
      union
      select 'B' Typ
      union
      select 'C' Typ
     ) x;



declare @PivotQuery nvarchar(max)
      , @Typ1 nvarchar(max)
      , @Typ2 nvarchar(max);

select @Typ1 = string_agg (QUOTENAME (Typ), ', ') from #Typ1;

select @Typ2 = string_agg (QUOTENAME (Typ), ', ') from #Typ2;


set @PivotQuery = 'select *
                   into #X
                   from 
                   (
                    select name, type1, type2, value from #Data
                   ) as SRC
                   pivot
                   (
                    max(Value)
                    for Type1 in (' + @Typ1 + ')
                   ) as PVT1;
                   
                   select * from #X;';

EXECUTE sp_executesql @PivotQuery;

List of Type1 is constant (always Short and Long) but list of Type2 will by changed (sometimes will A-C, sometimes A-F etc.)

I got pivoted by Type1 but no idea whats next

This assumes you have a separate table with the Types in it, as your expected results has a value for 'C' for Type2 but that isn't in your data. Your real table might look different, but you'll need to adjust for that.

As mentioned in the comments, PIVOT isn't ideal here. Switch to conditional aggregation, and this is much easier; you can just check if the 2 columns in your table equal that of the value(s) from your types table(s). Of course, doing this in the application layer to start with is likely even easier; if you don't understand what you're doing with dynamic SQL you shouldn't be using it, as it's a great avenue for breaking things.

CREATE TABLE dbo.Type (TypeType int,
                       TypeName varchar(10));
INSERT INTO dbo.Type (TypeType,TypeName)
VALUES (1,'Long'),
       (1,'Short'),
       (2,'A'),
       (2,'B'),
       (2,'C'),
       (2,'D'),
       (2,'E'),
       (2,'F');
GO

DECLARE @SQL nvarchar(MAX),
        @CRLF nchar(2) = NCHAR(13) + NCHAR(10);

DECLARE @Delimiter char(3) = N',' + @CRLF;

SELECT @SQL = N'SELECT Name,' + @CRLF + 
              STRING_AGG(N'       MAX(CASE WHEN Type1 = ' + QUOTENAME(T1.TypeName,'''') + N' AND Type2 = ' + QUOTENAME(T2.TypeName,'''') + N' THEN Value END) AS ' + QUOTENAME(T1.Typename + N'.' + T2.TypeName),@Delimiter) WITHIN GROUP (ORDER BY T1.TypeName, T2.TypeName) + @CRLF +
              N'FROM dbo.Test' + @CRLF +
              N'GROUP BY Name' + @CRLF +
              N'ORDER BY Name;'
FROM dbo.Type T1
     CROSS JOIN dbo.Type T2
WHERE T1.TypeType = 1
  AND T2.TypeType = 2;
  
EXEC sys.sp_executesql @SQL;

db<>fiddle