Pivot Table with Dynamic

Question

My quest might be answered in somewhere here but I couldn't find. So, sorry if I asked in vain.

I have a Table that's populate automatically with precisely date/time in SQL Server and looks like this:

Cod_Analise| Dat_analise     | Nom_usuario| Resultado
-----------+-----------------+------------+-----------
  1        | 02/20/2019 14:30|   John     |     4.5
  2        | 02/20/2019 14:31|   Carl     |     60
  3        | 02/21/2019 17:25|   Carl     |     17
  2        | 02/19/2019 06:00|   Marcus   |     58
  1        | 02/20/2019 15:40|   Jorge    |     5.2
  2        | 02/21/2019 22:00|   John     |     58

and I need something like this:

Dat_Analise | 1 | 2 | 3 | Nom_usuario
------------+---+---+---+------------
02/19/2019  | 0 |58 | 0 | Marcus     
02/20/2019  |4.9|60 | 0 | (First or Last one)

I need to do a pivot table based in this table where the Columns are Dat_Analise(date), Nom_operador(who did) and "Cod_Analise"(what did). And the rows are "Resultados"(results).

My problem is, i need to group by time period taking avg results for dynamic number of Cod_analises. But I even did the pivot with dynamic columns but I cannot fit the Group By part inside the pivot table.

I try to use a model that i found here and my procedure is like this:

SELECT
    A.[RESULTADO],
    A.[DAT_ANALISE],
    A.[NOM_USUARIO],
    B.[NOM_ANALISE]
into #temporaria
    FROM 
    [BSDB_Processo].[dbo].[RESULTADOS_ANALISES] A,
    [BSDB_Processo].[dbo].[ANALISES] B
WHERE
    A.COD_PROCESSO = @PROCESSO
AND
    A.COD_ANALISE = B.COD_ANALISE
AND 
    NUM_LOTE =@LOTE

Then:

declare @columnsSrc nvarchar(max) = N'' 
        ,@columnsDst nvarchar(max) = N'' 
        ,@sql        nvarchar(max)
        ,@KeyColumns nvarchar(max) = N'DAT_ANALISE'
        ,@compatibility int = (
           select top 1 compatibility_level from sys.databases
           where name = db_name()
           order by Name
      );
declare @GroupBy nvarchar(max) = 
--case when @compatibility <= 90 
--   then case when len(@KeyColumns)=0 then '' else 'group by ' + @KeyColumns + 
--       ' with rollup' end
--   else case when len(@KeyColumns)=0 then '' else 'group by rollup (' 
--       + @KeyColumns + ')'    end
--   end
 case when len(@KeyColumns)=0 then '' else 'group by ' + @KeyColumns end;

select  
    @columnsSrc += nchar(10) + N',' + quotename([NOM_ANALISE])
   ,@columnsDst += nchar(10) + N',sum(isnull(' + quotename([NOM_ANALISE]) + N',0)) as ' 
                                               + quotename([NOM_ANALISE])
  from (
      select [NOM_ANALISE]
      from  #temporaria
      group by [NOM_ANALISE]
  ) as x
  order by x.[NOM_ANALISE]    

And:

set @sql = N'
select ' + 
  case when len(@KeyColumns)=0 then '' else @KeyColumns + ',' end +
  STUFF(@columnsDst, 1, 2, '') + '
  INTO ##tabelaAnalises
from (
  select' + nchar(10) + 
    case when len(@KeyColumns)=0 then '' else @KeyColumns + ',' end +
'      [NOM_ANALISE],[RESULTADO]
   from #temporaria
) as j
pivot (
  sum([RESULTADO]) for [NOM_ANALISE] in ('
  + stuff(replace(@columnsSrc, ',p.[', ',['), 2, 1, '')
  + ')
) as p' + nchar(10) +
@GroupBy +
';'
>;

--print @sql;
exec sp_executesql @sql;
select  * from ##tabelaAnalises
commit
End

Hope you can help me guys and ,again, sorry if i did something wrong with this post. First time using this

Answer 1

Try to see the below query. Please, see UPDATE section of a pivot with column Nom_usuario.

Sample data:

IF OBJECT_ID('tempdb..#SomeTable') IS NOT NULL DROP TABLE #SomeTable
GO

CREATE TABLE #SomeTable
(
Cod_Analise int, 
Dat_analise datetime,    
Nom_usuario varchar(50),
Resultado numeric(18,1)
)

INSERT INTO #SomeTable
(
    Cod_Analise,
    Dat_analise,
    Nom_usuario,
    Resultado
)
VALUES
  (  2, '20190219 06:00', 'Marcus', 58)
, (  1, '20190220 14:30', 'John', 4.5) 
, (  2, '20190220 14:31', 'Carl', 60)
, (  1, '20190220 15:40', 'Jorge', 5.2)
, (  3, '20190221 17:25', 'Carl', 17)
, (  2, '20190221 22:00', 'John', 58)

A query:

SELECT 
pvt.Dat_analise
, pvt.[1]
, pvt.[2]
, pvt.[3] 
FROM 
    (SELECT 
      CONVERT(date, (t.Dat_analise)) Dat_analise
    , t.Cod_Analise 
    , t.Resultado
    FROM #SomeTable t) AS t
PIVOT
(
    AVG(T.Resultado)
    FOR t.Cod_Analise IN ([1], [2], [3])
) pvt

And dynamic version:

declare @cols nvarchar(max);
declare @sql  nvarchar(max);
  select @cols = stuff((
    select distinct 
      ' , ' + CONCAT('[', CONVERT(varchar(10),  t.Cod_Analise), ']')
      from #SomeTable t 
      order by 1
      for xml path (''), type).value('.','nvarchar(max)')
    ,1,2,'')

select  @sql = '
 select p.Dat_Analise,' + @cols + '
  from  (
    SELECT 
      CONVERT(date, (t.Dat_analise)) Dat_analise
    , t.Cod_Analise 
    , t.Resultado
    FROM #SomeTable t
      ) as t
 pivot (AVG(T.Resultado)
    FOR t.Cod_Analise in (' + @cols + ') ) p'


exec(@sql);

OUTPUT:

Dat_analise   1             2         3
2019-02-19  NULL        58.000000   NULL
2019-02-20  4.850000    60.000000   NULL
2019-02-21  NULL        58.000000   17.000000

UPDATE:

Use the following code snippet to show a Nom_usuario:

declare @cols nvarchar(max);
declare @sql  nvarchar(max);
select @cols = stuff((
    select distinct 
      ' , ' + CONCAT('[', CONVERT(varchar(10),  t.Cod_Analise), ']')
      from SomeTable t 
      order by 1
      for xml path (''), type).value('.','nvarchar(max)')
    ,1,2,'')

select  @sql = '
 select *
 from  
 (
    SELECT 
        CONVERT(date, (t.Dat_analise)) Dat_analise
    , t.Cod_Analise 
    , t.Resultado    
    , MAX(t.Nom_usuario) OVER (PARTITION BY CONVERT(DATE, (t.Dat_analise))) Nom_usuario
    FROM SomeTable t    
 ) as t
 pivot (AVG(T.Resultado)
    FOR t.Cod_Analise in (' + @cols + ') ) p'


exec(@sql);

OUTPUT:

Dat_analise    Nom_usuario      1              2              3
2019-02-21       John          NULL        58.000000      17.000000
2019-02-20       Jorge         4.850000    60.000000      NULL
2019-02-19       Marcus        NULL        58.000000      NULL