Данные сводной таблицы в табличном формате, первый столбец должен быть именем столбца, а остальные должны быть значением

Поскольку вы пытаетесь агрегировать атрибуты, которые существуют в нескольких столбцах, это указывает на то, что вам необходимо применить как UNPIVOT и PIVOT.

UNPIVOT возьмет значения ваших столбцов и преобразует их в строки. Код для разворота будет похож на этот:

select product_id, header, value
from
(
  select product_id, 
    product_name, 
    cast(product_price as varchar(10)) product_price, 
    product_weight
  from tblProduct
) p
unpivot
(
  value
  for header in (product_name, product_price, product_weight)
) unp

См. SQL Fiddle с демонстрацией. Вы заметите, что здесь есть подзапрос, который преобразует столбец product_price в varchar. Это связано с тем, что типы данных столбцов, которые вы хотите использовать в строках, должны быть одинаковыми. Поэтому вам, возможно, придется выполнить преобразование данных, чтобы заставить это работать правильно.

Unpivot генерирует результат, который выглядит следующим образом:

| PRODUCT_ID |         HEADER | VALUE |
---------------------------------------
|        141 |   product_name |  A141 |
|        141 |  product_price |   200 |
|        141 | product_weight | 200gm |

Как только данные находятся в строках, вы можете применить функцию PIVOT к значениям столбца product_id.

select header, [141], [142], [143], [144]
from
(
  select product_id, header, value
  from
  (
    select product_id, 
      product_name, 
      cast(product_price as varchar(10)) product_price, 
      product_weight
    from tblProduct
  ) p
  unpivot
  (
    value
    for header in (product_name, product_price, product_weight)
  ) unp
) d
pivot
(
  max(value)
  for product_id in ([141], [142], [143], [144])
) piv

См. SQL Fiddle с демонстрацией. Это дает результат:

|         HEADER |   141 |   142 |   143 |   144 |
--------------------------------------------------
|   product_name |  A141 |  A142 |  A143 |  A144 |
|  product_price |   200 |   300 |  4000 |  5000 |
| product_weight | 200gm | 300gm | 400gm | 100gm |

Приведенная выше версия будет отлично работать, если у вас есть известное количество значений product_id, которые вы хотите использовать в качестве столбцов. Но если у вас есть неизвестный номер, вам нужно будет реализовать динамический SQL:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(product_id) 
                    from tblProduct
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT header, ' + @cols + ' 
              from 
             (
                select product_id, header, value
                from
                (
                  select product_id, 
                    product_name, 
                    cast(product_price as varchar(10)) product_price, 
                    product_weight
                  from tblProduct
                ) p
                unpivot
                (
                  value
                  for header in (product_name, product_price, product_weight)
                ) unp
            ) x
            pivot 
            (
                max(value)
                for product_id in (' + @cols + ')
            ) p '

execute(@query)

См. SQL Fiddle с демонстрацией. Это приведет к тому же результату, что и статическая/жестко закодированная версия запроса.