Sql 2008 R2 - Select Field name and Field values

Question

Would like an T-SQL to do the following.

I would like to return the Field name and the Field data from a list of fields.

Let say I have a table called TableX which has fields ClientID, Surname, Firstname, Age, Sex. I only want to return ClientID, Surname and Firstname.

I want the following in a temp table to then scrutinize further

+-------------+--------------+------------+
| Client ID   |  FieldName   |  FieldData |
+-------------+--------------+------------+
| 1           |  Surname     |  "Smith"   |
| 1           |  Firstname   |  "Andrew"  |
+-------------+--------------+------------+

Answer 1

You can use union like this:

// drop the temp table if it exists
drop table #temptable

// use select...into to create a temporary table
select * into #temptable from
(
    select ClientID, 'Surname' as FieldName,  surname as FieldData from YourTable
    union all
    select ClientID, 'Firstname' as FieldName,  firstname as FieldData from YourTable
) s

// display the results...
select * from #temptable

The result will be this:

ClientID    FieldName FieldData
----------- --------- --------------------
1           Firstname Andrew
1           Surname   Smith