SQL Using specific input find corresponding columns and create new summary table

Question

Data is a flat normalised table:

|ID     |   Product selected    |   Product Code 1  |   Product Code 2  |   Product Code 3  | Cost of Product 1 | Cost of Product 2 | Cost of Product 3 | Rate of Product 1 | Rate of Product 2 | Rate of Product 3 |
|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|1      |       ABCDEDFHIJKL    |   AAABBBCCCDDD    |   ABCDEDFHIJKL    |   DDDCCCBBBAAA    |       995         |       495         |       0           |       4.4         |       6.3         |       7.8         |
|2      |       DDDCCCBBBAAA    |   AAABBBCCCDDD    |   ABCDEDFHIJKL    |   DDDCCCBBBAAA    |       995         |       495         |       0           |       4.4         |       6.3         |       7.8         |

What:

Using the product selected (ABCDEDFHIJKL), look across the rows to find the corresponding locations of columns with data relating to the product selected.

Desired Output:

|   Product selected    | Cost of Product   | Rate of Product   | 
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|       ABCDEDFHIJKL    |       495         |       6.3         |
|       DDDCCCBBBAAA    |       0           |       7.8         |

To do this in R is straight forward, and i'm sure for someone more knowledgable in SQL than I, this will be easy

Answer 1

Either use unpivot or union or crossapply

Unpivot sample

SELECT [Product selected], ProductCode ,ProductCost,ProductRate 
FROM
(
  SELECT *
  FROM dbo.table
) AS cp
UNPIVOT 
(
  ProductCode FOR PC IN ([Product Code 1], [Product Code 2], [Product Code 3])
) AS up

   UNPIVOT 
    (
      ProductCost FOR Po IN ([Cost of Product 1], [Cost of Product 2], [Cost of Product 3])
) AS up2

   UNPIVOT 
    (
  ProductRate FOR Pr  IN ([Rate of Product 1], [Rate of Product 2], [Rate of Product 3])
) AS up3;

Answer 2

You can use cross apply:

select t.product_selected, x.cost_of_product, x.rate_of_product
from mytable t
cross apply (values 
    (product_code_1, cost_of_product_1, rate_of_product_1),
    (product_code_2, cost_of_product_2, rate_of_product_2),
    (product_code_3, cost_of_product_3, rate_of_product_3)
) as x(product_selected, cost_of_product, rate_of_product)
where x.product_selected = t.product_selected