T-SQL - how to swap rows and columns

Question

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!

Answer 1

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'

Answer 2

this was always such a chore pre sql server 2005.

now i use PIVOT/UNPIVOT

Answer 3

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'