T-SQL - how to swap rows and columns

I have a resultset structure like this

ID       Value   Name
1    Oranges  Reponse
1    42  Count
2    Apples  Reponse
2    65  Count
3    Figs  Reponse
3    74  Count

and I want to get to this:

ID     Response       Count
1    Oranges  42
2    Apples  65
3    Figs  74

using SQL. Is there a way to do this? thanks!

From stackoverflow

• SELECT a.ID, a.Value AS [Response], b.Value AS [Count]
  FROM your_table AS a
      INNER JOIN your_table AS b
          ON a.ID = b.ID
  WHERE a.Name = 'Response'
      AND b.Name = 'Count'
  

  Sung Meister : +1 Ah, self-join...

• this was always such a chore pre sql server 2005.

  now i use PIVOT/UNPIVOT

• SELECT A.ID, A.VALUE RESPONSE , C.VALUE COUNT FROM _table A

  INNER JOIN (SELECT ID,VALUE , NAME FROM _table WHERE _table.Name = 'Count') C ON A.ID = C.ID WHERE A.NAME='Response' and C.NAME='Count'