SQL Server : Unpivot having whitespave and special chars on column to rows

Question

I am trying to unpivot columns to rows in SQL Server 2008.

I want to have whitespaces and special chars in the row values (transposed from columns).

One way is to do a Replace like, EmpName As 'Employee_Name' and in the Select do a Replace(Title, '_', ' '). But I have many special chars that needs to be addressed. Is there any way that I can address this?

The below query won't work because the selected values in the select statement are different than the names I use it under Unpivot.

Select 
    Title, Value 
from 
    (Select Top 1 
         EmpName AS 'Employee Name', EmpSalary AS 'Employee Salary**', Test As 'Test: !'
     From 
         Emp 
     WHERE EmpNo ='1234') p
Unpivot
    (Value For Title In (EmpName, EmpSalary, Test)) As unpvt;

Answer 1

Try using CROSS APPLY

SELECT Title, Value
FROM Emp
CROSS APPLY (
  VALUES
    ('Employee Name', EmpName),
    ('Employee Salary**', EmpSalary),
    ('Test: !', Test)
) upiv (Title, Value)
WHERE EmpNo ='1234'

or with UNPIVOT

SELECT Title, Value
FROM (
    SELECT [Employee Name] = EmpName, [Employee Salary**] = EmpSalary, [Test: !] = Test
    FROM Emp
    WHERE EmpNo ='1234'
) tbl
UNPIVOT(
    Value FOR 
    Title IN ([Employee Name], [Employee Salary**], [Test: !])
) upiv