Need a Query to output data in a specific format
-
We have a data set from SQL. We need to move the "Team" values for all matching ObjectIDs into one row. I have an image of what is needed, Fig.1
If possible, we need to have the output formatted as the "DesiredOutput" section in the Fig.1.
The query that we have now is:
SELECT
Personnel.ObjectID
,Personnel.LastName
,Personnel.FirstName
,Personnel.MiddleName
,Assigned.Team
FROM Access.PersonnelTeam
INNER JOIN Access.Personnel
ON PersonnelTeam.PersonnelID = Personnel.ObjectID
INNER JOIN Access.Team
ON PersonnelTeam.TeamID = Team.ObjectID
ORDER BY Personnel.ObjectIDFig.1
Fig.1
-
Are you able to provide your sample data in a format which we can cut & paste into SSMS, please?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Thank you Phil. Please see attached.
-
I can't execute that 🙂
I mean like this
DROP TABLE IF EXISTS #Personnel
CREATE TABLE #Personnel (<column definitions>)
INSERT #Personnel
VALUES (),(),() --your sample data
--Same for #PersonnelTeam and #TeamDoing it like this will mean that others can quickly pull a working query together for you.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
I apologize, new to this.
I do not know how to do what you are asking. Again, I am sorry, but I do appreciate your effort. Thank you.
-
Probably something using pivot. Knowing how many possible team columns can make it a bit more straightforward, but it can be done dynamically with more effort. There are examples via google (sql pivot unknown number of rows to columns) that have a couple different strategies. Here's the basic example: https://docs.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver15
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply