How to pivot wider these dataset-CodePudding

My test dataset

IdCx FecCx       OrderId Value
1234 2022-08-15     1    07:50
1234 2022-08-15     2    08:00
1234 2022-08-15     3    08:24
5678 2022-08-16     1    14:45
5678 2022-08-16     3    15:30

I require to pivot wider based on OrderId and Value My expected result will look like (I do need the NULL in Val2)

IdCx FecCx       Val1  Val2  Val3
1234 2022-08-15  07:50 08:00 08:24
5678 2022-08-16  14:45 NULL  15:30

My first approach has been with CASE but resulting dataset will not coalesce rows, leaving a lot of undesired nulls

My dbFiddle

CodePudding user response：

You simply need to aggregate - this collapses your NULL values into one row per group:

select idCx, FecCx
  ,max(case when OrderId = 1 then Value end) as Val1
  ,max(case when OrderId = 2 then Value end) as Val2
  ,max(case when OrderId = 3 then Value end) as Val3
from dbo.fact1
group by idCx, FecCx;

See modified Fiddle

CodePudding user response：

Just in case ORderID is not sequential, we use the window function row_number() over() in concert with a PIVOT

Example or dbFiddle

Select *
 From  ( 
         Select IDcx
               ,FecCx
               ,Item  = concat('Val',row_number() over (partition by idcx,FecCx order by OrderID) )
               ,Value
          From  fact1
        ) src
 Pivot ( max(Value) for Item in ([Val1],[Val2],[Val3])) pvt