Displaying columns as rows in a complex Select statement - sql

Question

I have two tables (a base table and a data table) which I am merging using OUTER APPLY to get the result.

tblBase looks like this:

+------+------+
| IDnu | Name |
+------+------+
|  1   | abc  |
|  2   | cde  |
|  3   | efg  |
|  4   | rfl  |
+------+------+

tblData is this:

+------+--------+--------+--------+-------------+
| IDNu | Price1 | Price2 | Price3 | ProductType |
+------+--------+--------+--------+-------------+
|    1 |     10 |     15 |     20 | Old         |
|    2 |     10 |     20 |     30 | Refurbished |
|    3 |     20 |     30 |     40 | New         |
|    1 |     20 |     15 |     20 | New         |
|    2 |     20 |     10 |     30 | Old         |
+------+--------+--------+--------+-------------+

My current query which calculates the tblData based on few criteria is as below:

Select IDNu, Name, t2.PNew, t2.POld FROM tblBase as t1
OUTER APPLY
(
SELECT
  SUM (CASE WHEN ProductType = 'New' THEN Price1 + Price2 ELSE 0 END) AS PNew, 
  SUM (CASE WHEN ProductType = 'Old' THEN Price2 + Price3 ELSE 0 END) AS POld
FROM tblData
WHERE IDNu = t1.IDNu
GROUP BY IDNu
) t2

The above query results in:

+------+------------+------+------+
| IDNu |    Name    | PNew | POld |
+------+------------+------+------+
|    1 | abc        | 35   | 35   |
|    2 | cde        | 0    | 40   |
|    3 | efg        | 50   | 0    |
|    4 | rfl        | NULL | NULL |
+------+------------+------+------+

Now my question is, instead of showing PNew and POld in two columns, show them in rows? Like this:

+------+------------+-------------+-------+
| IDNu |    Name    | ProductType | Price |
+------+------------+-------------+-------+
|    1 | abc        | PNew        | 35    |
|    2 | cde        | PNew        | 0     |
|    3 | efg        | PNew        | 50    |
|    4 | rfl        | PNew        | NULL  |
|    1 | abc        | POld        | 35    |
|    2 | cde        | POld        | 40    |
|    3 | efg        | POld        | 0     |
|    4 | rfl        | POld        | NULL  |
+------+------------+-------------+-------+

Answer 1

Can you try this:

SELECT tblBase.*, 'PNew' as ProductType, tblData.Price1 + tblData.Price2 as Price
 FROM tblBase left join `tblData`
 On tblBase.IDnu = tblData.IDnu AND tblData.ProductType = 'New' 
UNION ALL
SELECT tblBase.*, 'POld' as ProductType, tblData.Price3 + tblData.Price2 as Price
 FROM tblBase left join `tblData`
 On tblBase.IDnu = tblData.IDnu AND tblData.ProductType = 'Old'