how to convert a table to another in SQL (similar to pivot, but not exactly)

Question

I have a database table looks like this in the below, in SQL Server 2016:

   ProjectKey  -   Type    -    Percentage
   ----------------------------------------
   40               8            100%
   50               6            40%
   50               9            60%
   60               3            30%
   60               8            30%
   60               9            40%

(the max rows for the same ProjectKey is 3)

I want to write a query to be able to convert the above table to the following:

   ProjectKey   - Type1  -  Percentage1  - Type2  - Percentage2  - Type3  - Percentage3
   -------------------------------------------------------------------------------------
   40              8         100%           null     null           null    null
   50              6         40%            9        60%            null    null
   60              3         30%            8        30%            9       40%

If it can be achieved by writing a SQL query that would be great. Anyone can help? Thank you very much!

Answer 1

You can use row_number() and conditional aggregation:

select projectkey,
       max(case when seqnum = 1 then type end) as type_1,
       max(case when seqnum = 1 then percentage end) as percentage_1,
       max(case when seqnum = 2 then type end) as type_2,
       max(case when seqnum = 2 then percentage end) as percentage_2,
       max(case when seqnum = 3 then type end) as type_3,
       max(case when seqnum = 3 then percentage end) as percentage_3
from (select t.*,
             row_number() over (partition by projectkey order by type) as seqnum
      from t
     ) t
group by projectkey;