November 28, 2015 at 8:34 am
Hello.
I'm trying to create this query, where i have let's say a table persons (id,name,etc.), another table jobs(id,name,etc.) and finally another table that relate the two tables let's call it personjobs(idPerson,idJob,field1,field2)...
Now i want to create a query that returns all the persons, with all the jobs in the columns names (pivot), and for each personJob row (if any at the beginning), i need to fill the correct columns with field1 for example.
Right now i can't provide any sample data, later when i get to my computer i'll do that.
But the layout should be something like this:
Name | Custom Fields | Job1 | Job2 | ...
P1 | .................. | NULL | Field1 | ....
P2 | ...................| Field1 | Field1| ....
Thanks
November 28, 2015 at 9:25 am
From what you describe, it sounds like your tables are in fact joined together. So you want your job names as columns, right? What does the query return for a matched person/job versus a non-matched? I would guess a NULL for non-matched.
Please post your DDL along with your sample data when you get it together.
November 28, 2015 at 9:55 am
NULL when there's no relation.
Here's some sample data:
IF OBJECT_ID('tempdb..#Person','U') IS NOT NULL
BEGIN
DROP TABLE #Person
DROP TABLE #Job
DROP TABLE #PersonalJobs
END
CREATE TABLE #Person(
id INT PRIMARY KEY,
name NVARCHAR(50)
)
CREATE TABLE #Job(
id INT,
descr NVARCHAR(50)
)
CREATE TABLE #PersonJobs(
idP INT,
idJ INT,
startDate SMALLDATETIME
)
INSERT INTO #Person
SELECT 1,'PersonA'
UNION ALL
SELECT 2,'PersonB'
UNION ALL
SELECT 3,'PersonC'
INSERT INTO #Job
SELECT 1,'Builder'
UNION ALL
SELECT 2,'Painter'
UNION ALL
SELECT 3,'Destroyer'
INSERT INTO #PersonJobs
SELECT 1,1,GETDATE()
I created a row for PersonJobs, but in the first run it will be empty.
Thanks
November 28, 2015 at 10:12 am
By the way, i came with this, but this only count/sums whatever for the column value, i need to get the original value from the possible relation between the tables
DECLARE @Cols NVARCHAR(MAX)
DECLARE @Sql AS NVARCHAR(MAX)
SET @Cols = STUFF((SELECT ',' + QUOTENAME(descr) FROM #Job FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)'),1,1,'')
SET @Sql = 'SELECT * FROM
(SELECT P.id , P.name , J.descr
FROM #Person P LEFT OUTER JOIN #PersonJobs PJ ON P.id = PJ.idP
LEFT OUTER JOIN #Job J ON J.id = PJ.idJ) QR
PIVOT(
SUM(id)
FOR descr IN (' + @Cols +')
) PV'
EXEC sys.sp_executesql @Sql
Thanks
November 28, 2015 at 10:42 am
So my problem it's that the pivot function needs an aggregate function to work, but what i need it's the current field value...
In this case it could be anything, some cases it can be a date,or a text, or even a integer (id), it really depends in the job type.
Can this be done by the pivot function, or i need to create some memory table to try do the job?
Thanks
November 28, 2015 at 12:28 pm
rootfixxxer (11/28/2015)
So my problem it's that the pivot function needs an aggregate function to work, but what i need it's the current field value...In this case it could be anything, some cases it can be a date,or a text, or even a integer (id), it really depends in the job type.
Can this be done by the pivot function, or i need to create some memory table to try do the job?
Thanks
Add a bit more test data, especially for the #PersonJobs table that you posted and what you'd actually like the output to look like for the test data and we'll show ya how to do it.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 28, 2015 at 1:30 pm
In the "beginning" none of the persons will have formation in the jobs list, so the table will be empty...
When the persons get some kind of training in that job in the end of the training, someone will write the info to the table. Then it will have a start date and for some jobs, it will have additional properties, for example level of experience, etc...
The "return" datatype of the table depends of the kind of report the end user want, the main purpose it's knowing who is capable of doing some job, but that isn't the only report.
The output will be similar to the dynamic query that i wrote, but for simplicity, just assume that i need to return the date from the #PersonJobs table instead of Sum/Count/Whatever Aggregated Function.
Thanks
December 1, 2015 at 6:47 am
DECLARE @Cols NVARCHAR(MAX)
DECLARE @Sql AS NVARCHAR(MAX)
SET @Cols = STUFF((SELECT ',' + QUOTENAME(descr) FROM #Job FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)'),1,1,'')
SET @Sql = 'SELECT * FROM
(SELECT P.id , P.name , J.descr, PJ.startDate
FROM #Person P LEFT OUTER JOIN #PersonJobs PJ ON P.id = PJ.idP
LEFT OUTER JOIN #Job J ON J.id = PJ.idJ) QR
PIVOT(
max(startDate)
FOR descr IN (' + @Cols +')
) PV'
EXEC sys.sp_executesql @Sql
Is this what you required?
Regards
VG
December 1, 2015 at 8:00 am
No...
So my problem it's that the pivot function needs an aggregate function to work, but what i need it's the current field value...
In this case it could be anything, some cases it can be a date,or a text, or even a integer (id), it really depends in the job type.
Can this be done by the pivot function, or i need to create some memory table to try do the job?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply