SQL Server:查询多行，返回一行

问题
我们目前正在处理一些存储在下面的屏幕截图中的数据.我们使用它在 C# 应用程序中显示信息.我们让应用程序根据需要工作的唯一方法是将信息放在一行中.

我们在

SQL Server:动态透视 5 列

透视动态列，无聚合

https://www.simple-talk.com/blogs/2007/09/14/pivots-with-dynamic-columns-in-sql-server-2005/

SQL Server 动态 PIVOT 查询?

SQL Server 中的动态枢轴列

使用动态列的 SQL Pivot 查询

Problem
We are currently working with some data which is stored as in the screenshot below. We are using this to display information in a C# application. The only way we can get the application to work as needed is to have the information in one single row.

We have tried some methods on Display multiple rows and column values into a single row, multiple column values to no avail.

Desired output
We require each row with the same date and vehicleID to be on the same row to be output from our stored procedure.

Any help would be greatly appreciated. Cheers.

解决方案

if I understood correctly, you don't know how many unique date/vehicle_id combinations there are in the data. hence you can't just do a pivot over a set list of values.

in this case, you would need to build up a list of the date/vehicle ids dynamically, and then pivot over that list.

please check out the following examples involving dynamic SQL and pivot tables:

PIVOT in sql 2005

SQL Server : dynamic pivot over 5 columns

Pivot Dynamic Columns, no Aggregation

https://www.simple-talk.com/blogs/2007/09/14/pivots-with-dynamic-columns-in-sql-server-2005/

SQL Server dynamic PIVOT query?

Dynamic Pivot Columns in SQL Server

SQL Pivot Query with Dynamic Columns