SQL question using PIVOT, no aggregation column

Question

I would like to do a PIVOT on my table but I don't have aggregation column.

SELECT 
    CONVERT(date, DataHora) AS dia,
    CONVERT(VARCHAR(5), DataHora, 108) AS hora
FROM 
    [clientes].[dbo].[Tb_Ponto]

Result:

2021-12-27  12:41
2021-12-28  12:42
2021-12-28  13:03
2021-12-28  14:08

I would like it to stay like this:

2021-12-27  12:41 |  NULL | NULL 
2021-12-28  12:42 | 13:03 | 14:08

Answer 1

By adding the window function row_number() over() to your query, then it becomes a small matter to pivot.

This assumes you have a know or maximum number of columns.

Select *
 From (
        SELECT dia   = CONVERT(date, DataHora)
              ,hora  = CONVERT(VARCHAR(5),DataHora,108)
              ,ColNr = row_number() over (partition by CONVERT(date, DataHora) order by DataHora)
          FROM [clientes].[dbo].[Tb_Ponto]
      ) src
 Pivot ( max(hora) for ColNr in ([1],[2],[3],[4],[5]) ) pvt

Answer 2

As your data is ordered by date and time, you can group it by date, add sequence numbers to rows in each group, and then perform a row-to-column transposition. If you use SQL pivot(… in(column_list)), you should specify column_list. The value can be fixed in a simple scenario. When there are many columns or the number of columns is indefinite, you need to write a stored procedure to dynamically generate the columns. The process is complicated. An alternative is to move data out of database and process it in Python or SPL. SPL, the open-source Java package, is easy to be integrated into a Java program and generate simple code. It can do this with only two lines of code:

	A
1	=MSSQL.query("SELECT CONVERT(date, DataHora) AS dia,CONVERT(VARCHAR(5), DataHora, 108)AS hora FROM [clientes].[dbo].[Tb_Ponto] ORDER BY 1,2")
2	=A1.derive("hora"/ranki(#2;#1):fn).pivot(#1;#3,#2)