Home > Enterprise >  T-SQL Pivot table not using IN and values from another coloumn in the pivot data based on MIN() & MA
T-SQL Pivot table not using IN and values from another coloumn in the pivot data based on MIN() & MA

Time:12-06

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
  • Related