T-SQL Pivot table not using IN and values from another column in the pivot data based on MIN() & MAX

user_6257317 0
Viewed 72

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
pivot-table tsql sql-server sql
2 Answers

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
user_15700000

The PIVOT operator is rather inflexible, and requires a fixed list of columns to pivot.

The answer given by @JohnCappelletti is good, but it has the downside of requiring an extra sort because of the opposing row-number.

Here is a solution that only requires one sort

Select [Job Description]
      ,[Most]  = max( case when NextValue IS NULL then worker end)
      ,[Least] = max( case when RN = 1 then worker end)
 From (
        Select * 
              ,RN = row_number() over (partition by [Job Description] order by [Years of Experience] asc)
              ,NextVal = LEAD([Years of Experience]) over (partition by [Job Description] order by [Years of Experience] asc)
         from YourTable
      ) A
 Group By [Job Description]

The value inside the LEAD must be a non-nullable value.

user_148689970
Related