Display cell value as column name using SQL

Question

I have got table as given below in database:

Name        Grade       Subject

Ami           HD         Java
Ami           D          C++
Bec           D          Java
Bec           P          C++

Is it possible to display it in format given below only using SQL:

Name       Java       C++

Ami        HD         D
Bec        D          P

I have tried hard, but couldn't find a solution.

Thank you.

Answer 1

Try this:

SELECT
  Name, 
  MAX(CASE Subject WHEN 'C++'  THEN Grade END) 'C++',
  MAX(CASE Subject WHEN 'Java' THEN Grade END) 'Java'
FROM @test
GROUP BY Name;

The problem with your table is that there is no numeric value you can aggregate your columns with, so you can use Min or max as an aggregate. You can see the query in action in Data Explorer here