I need to parse this table:

╔══════╦═══════════╦═══════════╦═══════════╗
║ Year ║ Cost_Mon1 ║ Cost_Mon2 ║ Cost_Mon3 ║
╠══════╬═══════════╬═══════════╬═══════════╣
║ 2022 ║      1176 ║      3970 ║       540 ║
║ 2023 ║       540 ║       540 ║      3716 ║
╚══════╩═══════════╩═══════════╩═══════════╝

To this format with efficient way (better performance)

╔══════╦═══════╦══════╗
║ Year ║ Month ║ Cost ║
╠══════╬═══════╬══════╣
║ 2022 ║     1 ║ 1176 ║
║ 2022 ║     2 ║ 3970 ║
║ 2022 ║     3 ║  540 ║
║ 2023 ║     1 ║  540 ║
║ 2023 ║     2 ║  540 ║
║ 2023 ║     3 ║ 3716 ║
╚══════╩═══════╩══════╝

CodePudding user response：

You could use a union approach:

SELECT Year, 1 AS Month, Cost_Mon1 AS Cost FROM yourTable
UNION ALL
SELECT Year, 2, Cost_Mon2 FROM yourTable
UNION ALL
SELECT Year, 3, Cost_Mon3 FROM yourTable
ORDER BY 1, 2;

CodePudding user response：

Since the performance is a need on your question i would consider two of the best performant approaches:

1. Unpivot

It only allows a single column to be unpivoted, it does not meet your needs.

2. Cross join (values)

It allows multiple columns, here is the sql and

2. Union all

There is multiple data reading. This happens because UNION ALL will scan the rows once for every subquery, which considerably decreases the efficiency of the query execution.