SQL Server : Querying Multiple Rows, Returning In One

Question

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.

Current Dataset

So if you "collapse" multiple rows into a single one - what should be used for the other columns (other than vehicleID and which date column exactly??) — marc_s
– marc_s, Commented Dec 27, 2013 at 16:46
"The only way we can get the application to work as needed is to have the information in one single row." Can't the application be changed? Doing this in the database sounds like the wrong way to do it. — Joachim Isaksson
– Joachim Isaksson, Commented Dec 27, 2013 at 16:49
Hi @marc_s JobDate is the date we are looking at here cheers. I have also suggested the application be changed but I'm told this can't be done. — Alec.
– Alec., Commented Dec 27, 2013 at 16:51

Community · Accepted Answer · 2017-05-23 12:11:33Z

2

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

edited May 23, 2017 at 12:11

CommunityBot

11 silver badge

answered Dec 27, 2013 at 17:35

user3133172

887 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Server : Querying Multiple Rows, Returning In One

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related