how to rotate a table-valued in SQL Server-CodePudding

I have a table above and I want to convert it to a table as shown below

What methods can I use to do this?

I tried using PIVOT but I don't know how to change the Score column

So I hope I can find a solution to the above problem

CodePudding user response：

It's hard to tell what you tried with PIVOT and why it didn't work, but:

SELECT Name, aa, bb, cc
  FROM dbo.YourTableName
  PIVOT 
  (
    MAX(score) FOR subjects IN ([aa],[bb],[cc])
  ) AS p;

Working example in this fiddle.

CodePudding user response：

You can do this by executing a dynamically creates query.

Query

declare @sql as varchar(max);
select @sql = 'select [name],'   stuff((
    select distinct 
    ',max(case [subjects] when '   char(39)   [subjects]   char(39) 
      
' then [score] end) ['   [subjects]   ']'
    from your_table_name
    for xml path('')
), 1, 1, '');

select @sql  = ' from your_table_name group by [name];';
exec(@sql);

Find demo here