How to join an unpivot resulting table with a dimension table?

user_7044446 0
Viewed 38

This is my test environment

select * from dbo.fact1
IdRecordRedcap FechaRegistro TotalPruebas TotalPruebasPositivas
1 2022-01-06 510 384
2 2022-01-07 300 184 
 select * from dbo.DimMeasures
IdDimMeasures NameMeasure
1 TotalPruebas
2 TotalPruebasPositivas

So far, I'm able to run the query which contains the UNPIVOT procedure but I require to join it with dbo.DimMeasures so I can get IdDimMeasures for each NameMeasure

--UNPIVOT dbo.fact1
SELECT  u.FechaRegistro,
    u.NameMeasure,
    u.ValueMeasure 
FROM dbo.fact1 as x

UNPIVOT 
  (
     ValueMeasure
     for NameMeasure IN
     (
        TotalPruebas,
        TotalPruebasPositivas
    ) 
  ) AS u
GO
FechaRegistro NameMeasure ValueMeasure
2022-01-06 TotalPruebas 510
2022-01-06 TotalPruebasPositivas 384
2022-01-07 TotalPruebas 300
2022-01-07 TotalPruebasPositivas 184

db<>fiddle

tsql sql-server sql
1 Answers
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
user_613050
Related