This is my test environment

select * from dbo.fact1

 select * from dbo.DimMeasures

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

db<>fiddle

CodePudding user response：

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:

• Example db<>fiddle (thank you for that!)