How to join an unpivot resulting table with a dimension table?
SELECT u.FechaRegistro,
u.NameMeasure,
u.ValueMeasure,
m.IdDimMeasures
FROM dbo.fact1 as x
UNPIVOT
(
ValueMeasure
for NameMeasure IN
(
TotalPruebas,
TotalPruebasPositivas
)
) AS u
LEFT OUTER JOIN dbo.DimMeasures AS m
ON m.NameMeasure = u.NameMeasure;
Output:
| FechaRegistro | NameMeasure | ValueMeasure | IdDimMeasures |
|---|---|---|---|
| 2022-01-06 | TotalPruebas | 510 | 1 |
| 2022-01-06 | TotalPruebasPositivas | 384 | 2 |
| 2022-01-07 | TotalPruebas | 300 | 1 |
| 2022-01-07 | TotalPruebasPositivas | 184 | 2 |
- Example db<>fiddle (thank you for that!)