没有聚合功能的SQL server pivot数据，并打印列中的所有值

Question

试着弄清楚如何写一个pivot sql server语句。我有以下表格

源表

ID |Product |Event        |Date 
-----------------------------------------------
1  |Laptop  |Search       |2020-07-17 14:41:13.535
1  |Laptop  |Product Page |2020-07-17 14:41:13.535
1  |Laptop  |Bought       |2020-07-17 14:41:13.535
1  |Tablet  |Search       |2020-07-18 14:41:13.535
1  |Tablet  |Product Page |2020-07-18 14:41:13.535
1  |Tablet  |Bought       |2020-07-18 14:41:13.535

所需输出

ID|   Product |Search                  |Product Page             |Bought
-----------------------------------------------------------------------------------------------
1 |   Laptop  |2020-07-17 14:41:13.535 |2020-07-17 14:41:13.535  |2020-07-17 14:41:13.535
1 |   Tablet  |2020-07-18 14:41:13.535 |2020-07-18 14:41:13.535  |2020-07-18 14:41:13.535

My Query look like

with V1 as 
(
select id,product,event,start_time_local
from table1
)
select id,product,
[search],[product page],[Bought] from V1
PIVOT (Max(start_time_local) for event_type in ([search],[product page],[Bought]))
as PivotTable;

当我取Max(date)时，它只返回我想显示所有日期的最大值。

Answer 1

正如我在评论中提到的，你不能没有聚合的PIVOT/Cross Tag，因为它是聚合的一种形式。然而，就我个人而言，我建议使用后者(也称为条件聚合)，因为它的限制要少得多。然后您可以执行以下操作:

SELECT ID,
Product,
MAX(CASE Event WHEN 'Search' THEN Date END) AS Search,
MAX(CASE Event WHEN 'Product Page' THEN Date END) AS ProductPage,
MAX(CASE Event WHEN 'BOught' THEN Date END) AS Bough
FROM (VALUES(1,'Laptop','Search      ','2020-07-17T14:41:13.535'),
(1,'Laptop','Product Page','2020-07-17T14:41:13.535'),
(1,'Laptop','Bought      ','2020-07-17T14:41:13.535'),
(1,'Tablet','Search      ','2020-07-18T14:41:13.535'),
(1,'Tablet','Product Page','2020-07-18T14:41:13.535'),
(1,'Tablet','Bought      ','2020-07-18T14:41:13.535'))V(ID,Product,Event,Date)
GROUP BY ID,
Product;