The easiest, in my opinion, is to build a relationship table, which can have two or more relationships, which will have to be exploited later. For example:
WITH CTE AS(
SELECT 111 as ID_ER_CONCEPTO_PPTO,
109 as ID_ER_CONCEPTO_PPTO_1,
110 as ID_ER_CONCEPTO_PPTO_2
)
SELECT T.ID_ER_CONCEPTO_PPTO,
T1.banorte,
T1.aspe,
T2.banorte,
T2.aspe
FROM CTE T
INNER JOIN #TBL_PASO_1 T1
ON T1.ID_ER_CONCEPTO_PPTO = T.ID_ER_CONCEPTO_PPTO_1
INNER JOIN #TBL_PASO_1 T2
ON T2.ID_ER_CONCEPTO_PPTO = T.ID_ER_CONCEPTO_PPTO_2
The basis is to precisely define the relationships, which could even be a physical table, in this example we define it "on the fly" through a CTE but it could also be a subquery:
SELECT 111 as ID_ER_CONCEPTO_PPTO,
109 as ID_ER_CONCEPTO_PPTO_1,
110 as ID_ER_CONCEPTO_PPTO_2
Basically the new IDone and a column for each IDrelated, then it's just a matter of making the JOINsright ones to transform the rows into columns.
The easiest, in my opinion, is to build a relationship table, which can have two or more relationships, which will have to be exploited later. For example:
The basis is to precisely define the relationships, which could even be a physical table, in this example we define it "on the fly" through a CTE but it could also be a subquery:
Basically the new
ID
one and a column for eachID
related, then it's just a matter of making theJOINs
right ones to transform the rows into columns.