SQL Question Using PIVOT, No Aggregation Column



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


CONVERT(date, DataHora) AS dia,

CONVERT(VARCHAR(5), DataHora, 108) AS hora




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


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:



=MSSQL.query("SELECT CONVERT(date, DataHora) AS   dia,CONVERT(VARCHAR(5), DataHora, 108)AS hora FROM   [clientes].[dbo].[Tb_Ponto] ORDER BY 1,2")




View SPL source code.