Unpivoting with Column headers - single row

Question

I have some info on a particular person which I want to show in two rows rather than multiple columns.

I would like the columns to be "ColumnName" and "ColumnData"

This is my query to return the single row:

SELECT C.CUSTOMER_NAME
      ,CC.CONTACT_NAME
      ,CC.TELEPHONE
      ,CC.FAX
      ,CC.CONTACT_INITIALS
      ,CC.CONTACT_FIRSTNAME
      ,CC.EMAIL
      ,CC.CONTACT_DEAR
      ,CC.NUMERIC_PHONE_NO
      ,CC.TELEPHONE_NUMBER2
      ,CC.MOBILE_TELEPHONE
      ,CC.NUMERIC_TELEPHONE2
      ,CC.NUMERIC_MOBILE
      ,CC.NUMERIC_FAX
      ,CC.CONTACT_FULL_NAME
      ,CONTACT_MIDDLE_NAMES
FROM table C 
INNER JOIN table CC
ON C.column = CC.column
WHERE C.column = @CustomerAccount

I have tried to unpivot this and have not managed to get this to work as there is no aggregation and there is only one value per row.

Whilst i can get the column names from sys.columns i can't relate them to the table and also would have to unpivot them.

Is there a way to turn this single row into two columns comprising of the column name and the data in that column?

Any helps, links or guidance would be appreciated.

Thanks

Will.

Answer 1

You can use the UNPIVOT like following query.

;WITH cte 
     AS (SELECT C.customer_name, 
                CC.contact_name, 
                CC.telephone, 
                CC.fax, 
                CC.contact_initials, 
                CC.contact_firstname, 
                CC.email, 
                CC.contact_dear, 
                CC.numeric_phone_no, 
                CC.telephone_number2, 
                CC.mobile_telephone, 
                CC.numeric_telephone2, 
                CC.numeric_mobile, 
                CC.numeric_fax, 
                CC.contact_full_name, 
                contact_middle_names 
         FROM   table C 
                INNER JOIN table CC 
                        ON C.COLUMN = CC.COLUMN 
         WHERE  C.COLUMN = @CustomerAccount) 
SELECT u.x AS ColumnName, 
       u.y AS ColumnValue 
FROM   cte s 
       UNPIVOT ( [y] 
               FOR [x] IN (customer_name, 
                           contact_name, 
                           telephone, 
                           fax, 
                           contact_initials, 
                           contact_firstname, 
                           email, 
                           contact_dear, 
                           numeric_phone_no, 
                           telephone_number2, 
                           numeric_mobile, 
                           numeric_fax, 
                           contact_full_name, 
                           contact_middle_names) ) u; 

Online Demo