Dynamic SQL to convert many columns to rows

I have a table that contains over 100 columns and I would like to keep 2 columns as fixed columns and transform the rest. I can do this with union but it will be a lots of work and long code. I would like a shorter SQL code to achieve same result.

SELECT 
material, plant, 'name' AS column_name, name AS column_value FROM materialtable 
UNION ALL 
SELECT 
material, plant, 'category' AS column_name, category AS column_value FROM materialtable 
UNION ALL 
SELECT material, plant, 'rating' AS column_name, CAST(rating AS STRING) AS column_value FROM materialtable UNION ALL 
SELECT 
material, plant, 'price' AS column_name, CAST(price AS STRING) AS column_value FROM materialtable

thanks

I have tried below code but having syntax error "Syntax error: Unclosed string literal at". I am working on Big query platform.

DECLARE query STRING;

SET query = (
  SELECT CONCAT(
    'SELECT * FROM (',
    STRING_AGG(
      FORMAT(
        'SELECT 
           material,
           plant,
           "%s" AS column_name,
           CAST(%s AS STRING) AS column_value
         FROM materialtable',
        column_name,
        column_name
      ),
      ' UNION ALL '
    ),
    ')'
  )
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE table_name = 'materialtable'
    AND column_name NOT IN ('material', 'plant')
  ORDER BY ordinal_position
)

EXECUTE IMMEDIATE query;
                              

This is happening because of newline between the static columns list:

Try this way:

BEGIN


DECLARE query STRING;
SET query = (
  SELECT CONCAT(
    'SELECT * FROM (',
    STRING_AGG(      FORMAT( 'SELECT    material,  plant, "%s" AS column_name, CAST(%s AS STRING) AS column_value   FROM materialtable',
        column_name,        column_name      ),      ' UNION ALL '    ),    ')'  )  FROM `region-us`.INFORMATION_SCHEMA.COLUMNS
  WHERE table_name = 'materialtable'AND column_name NOT IN ('material', 'plant')
  
);

EXECUTE IMMEDIATE query;  

END;