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?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
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