How to select fields from @SQL statement to UNPIVOT

Because of the flexible nature of SQL language, it would be hard to parse the column names just from a string. You would need to take into account things like
- Table aliases
- Column aliases
- Function usage
- Scalar queries
- and so on...

Few things that might work for you:

Store the list of columns or column names in a separate string and use that in both places.

identify the column name with some special character pattern. Use that pattern to find columns, for example

SELECT A.##BRN_NAME##, 
       A.##PROD_SERV##, 
       A.##COMP_TYPE##,
       P.##PROD_SERV_TAT##,
       COUNT(*) AS ##OVER_CNT##
FROM TRIAL

Now you can find the names between ##. Just remember to remove the hashes before using the statement.

ADDITION:
---------
When extracting the column names and using it elsewhere, depending on the SQL statements to be expected there are a lot of things you need to take into consideration. As said SQL is flexible in writing.

But to get you started with, consider the following example

CREATE TABLE #test2 (
  col1 int,
  col2 int
);

INSERT INTO #test2 VALUES 
(1,2),
(3,4);

Now if we create a function which would extract the column list from a simple case, it could look something like

CREATE FUNCTION ColList(@originalSql nvarchar(max)) RETURNS varchar(max) AS 
BEGIN
   DECLARE @list nvarchar(max),
           @index1 int,
		   @index2 int;

   SET @index1 = 0;
   SET @list = '';
   SET @index1 =CHARINDEX('##', @originalSql, 0) + 2;
   WHILE @index1 > 0 BEGIN
      SET @index2 =CHARINDEX('##', @originalSql, @index1 + 2);
      IF (@list != '') BEGIN
	     SET @list = @list + ', ';
	  END;
      SET @list = @list + SUBSTRING(@originalSql, @index1, @index2 - @index1);
      SET @index1 = CHARINDEX('##', @originalSql, @index2 + 2) ;
	  IF (@index1 > 0 ) BEGIN
	     SET @index1 = @index1 + 2
	  END;
   END;

   RETURN @list
END;

Let's have a test

DECLARE
  @sql nvarchar(max),
  @col nvarchar(max)
BEGIN
   SET @sql = 'SELECT t.##Col1##, t.##Col2## FROM #Test2 t';

   SET @col = dbo.ColList(@sql);
   SET @sql = 'SELECT ' + @col + ' FROM (' + REPLACE(@sql, '##','') + ') a';
   PRINT  @sql;
   EXEC (@sql);
END;

The query that is executed is as follows

SELECT Col1, Col2 FROM (SELECT t.Col1, t.Col2 FROM #Test2 t) a

But as said, that's just the starters. For example you may need to be able to define an alias for the columns used in the column list, and so on...