SQLite 聚合然後轉置

我目前正在使用 Linux 機器和臨時建構的 Perl 腳本將我的太陽能逆變器數據上傳到 PVOutput。我想添加來自我的能源供應商的儀表數據。我從我的能源供應商處下載了一個 87,000 行的 CSV 文件，其中包含以下（樣本）數據：

NMI,Meter Serial Number,Stream,Register Number,Register Description,UOM,ReadingDate,Units,Read Type<br>
700555,7006444,B,B1,Solar energy,kWh,23-09-21 23:45,0,      Actual
700555,7006444,E,E1,Offpeak,     kWh,23-09-21 23:45,0.093,  Actual
700555,7006444,B,B1,Solar energy,kWh,23-09-21 23:30,0,      Actual
700555,7006444,E,E1,Offpeak,     kWh,23-09-21 23:30,0.085,  Actual
700555,7006444,B,B1,Solar energy,kWh,23-09-21 23:15,0,      Actual
700555,7006444,E,E1,Offpeak,     kWh,23-09-21 23:15,0.085,  Actual

通過創建一個虛擬列 (MyDate) 並使用以下選擇：

select MyDate, "Register Description", sum(Units) 
from MeterData group by MyDate, "Register Description"

我已經能夠按摩 SQLite 中的數據給我：

20210920|Offpeak|3.821
20210920|Peak|4.954
20210920|Shoulder|5.695
20210920|Solar energy|2.958
20210921|Offpeak|3.831
20210921|Peak|4.41
20210921|Shoulder|4.428
20210921|Solar energy|4.354
20210922|Offpeak|4.355
20210922|Peak|6.572
20210922|Shoulder|1.961
20210922|Solar energy|7.217

但是對於上傳到 PVOutput，我正在尋找的格式是：

20210919|Offpeak|3.514|Peak|8.453|Shoulder|7.445|Solar energy|2.792
20210920|Offpeak|3.821|Peak|4.954|Shoulder|5.695|Solar energy|2.958
20210921|Offpeak|3.831|Peak|4.410|Shoulder|4.428|Solar energy|4.354
20210922|Offpeak|4.355|Peak|6.572|Shoulder|1.961|Solar energy|7.217

如您所見，我的 SQL 技能很弱，任何幫助將不勝感激。

菲爾。

簡單的條件聚合應該可以解決問題：

select
 MyDate,
 'Offpeak',
 sum(Units) filter (where "Register Description" = 'Offpeak'),
 'Peak',
 sum(Units) filter (where "Register Description" = 'Peak'),
 'Shoulder',
 sum(Units) filter (where "Register Description" = 'Shoulder'),
 'Solar energy',
 sum(Units) filter (where "Register Description" = 'Solar energy')
from MeterData
group by MyDate

另一種語法是刪除filter和使用case

sum(case when "Register Description" = 'Offpeak' then Units end)

引用自：https://dba.stackexchange.com/questions/300138