SQL Server Unpivot on Pair Columns

Question

I apologize for the poorly worded title, I was not sure how else to describe this question.

I have a table in SQL Server 2008 which looks like this, wonky schema aside:

I would like to process a query given a "name" to produce these results:

I have looked at a number of pivot table examples and have come up with inconclusive results. I have not had much practice in advanced, complex queries.

Edit: Here is an SQL Fiddle link: http://sqlfiddle.com/#!3/28f93/1

Answer 1

It's not clear what you want but here is a query to get your output results:

select 'DESCRIPTOR1' as Descr,
        DESCRIPTOR1A as A,
        DESCRIPTOR1B as B
from mytable
where Name='Bob'
UNION ALL
select 'DESCRIPTOR2' as Descr ,
        DESCRIPTOR2A as A,
        DESCRIPTOR2B as B 
from mytable
where Name='Bob'

SQL Fiddle demo

Or if you need it for all names:

select  Name,
        'DESCRIPTOR1' as Descr,
        DESCRIPTOR1A as A,
        DESCRIPTOR1B as B
from mytable

UNION ALL
select Name,
       'DESCRIPTOR2' as Descr ,
        DESCRIPTOR2A as A,
        DESCRIPTOR2B as B 
from mytable
ORDER BY 1,2

SQLFidle demo