Question

Source:https://stackoverflow.com/questions/70511664/sql-question-using-pivot-no-aggregation-column

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

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:

View SPL source code.