Make a one line data to a table in SQL

There is a record like

SWD Lead QA Lead PD Lead Business Manager
AAAA     BBBB    CCCC    DDDD

I want to use T-SQL to ge开发者_StackOverflow中文版t a result like below with the column name

SWD Lead         AAAA
QA Lead          BBBB
PD Lead          CCCC
Business Manager DDDD

Does anyone know how to do it ?

Best Regards,

Since you are using SQL Server 2005 you can use UNPIVOT. This is modified from the example on that page:

SELECT ColA, ColB
FROM Table1
UNPIVOT (ColB FOR ColA IN ([SWD Lead],
                           [QA Lead],
                           [PD Lead],
                           [Business Manager])) AS unpvt;

Result:

ColA              ColB
SWD Lead          AAAA
QA Lead           BBBB
PD Lead           CCCC
Business Manager  DDDD

On older server versions you could do something like this to achieve the same effect:

SELECT 'Col1', Col1 FROM Table1
UNION ALL
SELECT 'Col2', Col2 FROM Table1
UNION ALL
SELECT 'Col3', Col3 FROM Table1
UNION ALL
SELECT 'Col4', Col4 FROM Table1