text: Q:
  
  How to group ID's by score in different types in SQL Server
  
  I have a table like this:
  ID    Type    Score
  -------------------
  5      1       100
  8      1       200
  3      1       300 
  8      2       100
  3      2       200
  5      2       300
  
  How do I sort them by descending score (to give them a ranking for that Type) and then create a table which a column for each Type where the ID's positions are shown such as:
  ID    Type1    Type2
  --------------------
  3      1st      2nd
  5      3rd      1st
  8      2nd      3rd
  
  So far I am able able to do this by explicitly declaring the Type number such as:
  SELECT ROW_NUMBER() OVER(ORDER BY Score DESC) AS Rank, ID
  FROM Table
  WHERE Type = 1
  
  This returns a rank for each ID when Type is 1.
  How do I join this together with the same result when Type is 2? And how do I do this for any number of types?
  
  A:
  
  There are a number of ways to tackled this. My choice would be to use conditional aggregation. Here is how this might look. If you need a dynamic number of types that can be accomplished also but is a little trickier.
  declare @Something table
  (
      ID int
      , Type int
      , Score int
  )
  ;
  
  insert @Something values
  (5, 1, 100)
  , (8, 1, 200)
  , (3, 1, 300) 
  , (8, 2, 100)
  , (3, 2, 200)
  , (5, 2, 300)
  ;
  
  with SortedValues as
  (
      select *
          , RowNum = ROW_NUMBER() over (partition by Type order by Score)
      from @Something
  )
  select ID
      , Type1 = max(case when Type = 1 then RowNum end)
      , Type2 = max(case when Type = 2 then RowNum end)
  from SortedValues
  group by ID
  order by ID
  ;
  
  -- EDIT --
  I realized you said you need to have this work for any number of Types. Most people around SO like to ue a dynamic pivot. I personally find the syntax for pivot to be very obtuse. I prefer to build a dynamic version of conditional aggregation for this type of thing. Here is how you can use dynamic sql to generate the results for any number of Types.
  Note I had to switch to using a temp table because a table variable would not be available in the scope of the dynamic sql unless it is declared inside the dynamic sql.
  if OBJECT_ID('tempdb..#Something') is not null
      drop table #Something
  
  create table #Something
  (
      ID int
      , Type int
      , Score int
  )
  ;
  
  insert #Something values
  (5, 1, 100)
  , (8, 1, 200)
  , (3, 1, 300) 
  , (8, 2, 100)
  , (3, 2, 200)
  , (5, 2, 300)
  ;
  
  declare @StaticPortion nvarchar(2000) = 
      'with SortedValues as
      (
          select *, ROW_NUMBER() over(partition by Type order by Score) as RowNum
          from #Something
      )
      select ID';
  
  declare @DynamicPortion nvarchar(max) = '';
  
  with E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
  E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
  cteTally(N) AS 
  (
      SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E2
  )
  
  select @DynamicPortion = @DynamicPortion + 
      ', MAX(Case when Type = ' + CAST(N as varchar(6)) + ' then RowNum end) as Type' + CAST(N as varchar(6)) + CHAR(10)
  from cteTally t
  where t.N <= 
  (
      select Count(distinct Type)
      from #Something
  )
  
  declare @FinalStaticPortion nvarchar(2000) = ' from SortedValues
  group by ID
  order by ID';
  
  select @StaticPortion + @DynamicPortion + @FinalStaticPortion
  
  declare @SqlToExecute nvarchar(max) = @StaticPortion + @DynamicPortion + @FinalStaticPortion;
  
  select @SqlToExecute
  exec sp_executesql @SqlToExecute
  
  