I have a pivot table problem & would love help....
The tutorials I'm following aren't helping as they use an IN statement on a known list of values to create the columns.
It's my IN statement I might be stuck on...I'm trying a subquery, but it's not helping.
SELECT
JobDesc, YearExp
FROM
(SELECT JobDesc, YearExp, Worker
FROM Q2) AS SourceTable
PIVOT
(MIN(YearExp)
FOR YearExp IN (SELECT YearExp FROM Q2)
) AS PivotTable
The data:
Worker | Job Description | Years of Experience |
---|---|---|
1001 | Lawyer | 6 |
2002 | Lawyer | 12 |
3003 | Lawyer | 17 |
4004 | Doctor | 21 |
5005 | Doctor | 9 |
6006 | Doctor | 8 |
7007 | Scientist | 13 |
8008 | Scientist | 2 |
9009 | Scientist | 7 |
The output I'm trying to achieve:
Job Description | Most Experienced | Least Experienced |
---|---|---|
Lawyer | 3003 | 1001 |
Doctor | 4004 | 6006 |
Scientist | 7007 | 8008 |
CodePudding user response:
The window function row_number() over()
in concert with a conditional aggregation should do the trick
Select [Job Description]
,[Most] = max( case when RN1 = 1 then worker end)
,[Least] = max( case when RN2 = 1 then worker end)
From (
Select *
,RN1 = row_number() over (partition by [Job Description] order by [Years of Experience] desc)
,RN2 = row_number() over (partition by [Job Description] order by [Years of Experience] asc)
from YourTable
) A
Group By [Job Description]
Results
Job Description Most Least
Doctor 4004 6006
Lawyer 3003 1001
Scientist 7007 8008