My data in testtable:

I require to unpivot the following data without the UNPIVOT function

Structure and sample data in this db<>fiddle

CodePudding user response：

-- on SQL Server 2000 you must have a death wish

  SELECT FechaRegistro, IdRecordRedcap,
         Var   = 'diario_total_pruebas',
         Value =  diario_total_pruebas 
  FROM dbo.testtable

  UNION ALL

  SELECT FechaRegistro, IdRecordRedcap,
         Var   = 'diario_pruebas_negativas',
         Value =  diario_pruebas_negativas 
  FROM dbo.testtable

  UNION ALL

  SELECT FechaRegistro, IdRecordRedcap,
         Var   = 'diario_pruebas_rechazadas',
         Value =  diario_pruebas_rechazadas 
  FROM dbo.testtable;

To be fair, to have the UNPIVOT solution on versions published this century deal with NULLs correctly, it's not that much cleaner:

-- on SQL Server 2005 and later

;WITH x AS 
(
  SELECT FechaRegistro, IdRecordRedcap, 
    diario_total_pruebas      = COALESCE(diario_total_pruebas,      -1),
    diario_pruebas_negativas  = COALESCE(diario_pruebas_negativas,  -1),
    diario_pruebas_rechazadas = COALESCE(diario_pruebas_rechazadas, -1)
  FROM dbo.testtable
)
SELECT FechaRegistro, IdRecordRedcap, 
       Var, Value = NULLIF(Value, -1)
  FROM x UNPIVOT 
  (
     Value FOR Var IN
     (
       diario_total_pruebas,
       diario_pruebas_negativas,
       diario_pruebas_rechazadas
     )
  ) AS u;

Output in both cases:

• Example db<>fiddle

CodePudding user response：

UNPIVOT was introduced with SQL Server 2005. If you really use SQL Server 2000 you have far bigger problems than UNPIVOTing. A laptop with SQL Server Express is far more powerful with far faster disks, far more RAM and CPU cores than anything available 20 years ago (multicore wasn't a thing yet). And far safer too.

Without UNPIVOT you can have to write as many queries as there are columns to unpivot and combine them with UNION ALL :

select FechaRegistro,IdRecordRedcap,'diario_total_pruebas' as Var,
diario_total_pruebas as Value
from testtable
UNION ALL
select FechaRegistro,IdRecordRedcap,'diario_pruebas_negativas' as Var,
diario_pruebas_negativas   as Value
from testtable
...