Another transpose rows to columns question- SQL Server 2005

Sorry I know similar question have been asked many times before but like most I haven't been able to find one that fits what I need or gives me enough information to be able to expand towards the solution.

I suspect I could use the PIVOT or UNPIVOT commands of T-SQL but I can't follow the MSDN explanation and there don't seem to be any "idiots" type guides out there!

Onto my problem - I need to convert a wide table consisting of many columns to a row based result set for a report. No aggregation needed and I'm trying to avoid a repeated UNION based select if possible.

The table result set is formatted as such (there are actually many more person columns! :s ):

Person1 | Person2 | Person3 | Person4 | Person5 | Person6 | Person7 | Person8
-----------------------------------------------------------------------------
Bob       Sam       Tom       Alex      Paul      Ann       Jill      Jane

What I really need is to be able to produce the following:

Person
--------------------
Bob       
Sam       
Tom       
Alex      
Paul      
Ann       
Jill      
Jane

A bonus would be able to create a result set such as:

Column    Person
--------------------
Person1   Bob       
Person2   Sam       
Person3   Tom       
Person4   Alex      
Person5   Paul      
Person6   Ann       
Person7   Jill      
Person8   Jane

How can this be achieved using T-SQL in SQL Server 2005?

Thanks for any help,

Paul.

--Edit--

Thanks to Martin I've learnt something new this morning and I've managed to get exactly what I needed. In the end I h开发者_C百科ad to modify the example slightly to get what I needed but that's because my original example left out some detail that I hadn't realised would be important!

My final piece of code looked like this for anyone else that has such a problem:

WITH Query_CTE([Person1 Title],[Person2 Title],[Person3 Title]) 
AS
--CTE Expression and column list
(
    SELECT  
            --Converted to create a common data type. 
            CONVERT(NVARCHAR(MAX),Person1Title) AS 'Person1 Title',
            CONVERT(NVARCHAR(MAX),Person2Title) AS 'Person2 Title',
            CONVERT(NVARCHAR(MAX),Person3Title) AS 'Person3 Title'
FROM Table_Name
WHERE KeyId = 'XXX' 
)
SELECT *
FROM Query_CTE
UNPIVOT
   (Person FOR [Column] IN 
    (   
        [Person1 Title],
        [Person2 Title],        
        [Person3 Title]
    )
)AS unpvt;

---

WITH T(Person1,Person2 /* etc....*/) AS
(
SELECT 'Bob','Sam' /* etc....*/
)
SELECT *
FROM T
UNPIVOT
   (Person FOR [Column] IN 
      (Person1,Person2 /* etc....*/)
)AS unpvt;

---

To easily transpose columns into rows with its names you should use XML. In my blog I was described this with example: http://sql-tricks.blogspot.com/2011/04/sql-server-rows-transpose.html