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:

The output I'm trying to achieve:

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

CodePudding user response：

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.