Data formatting in MS SQL

Question

I tried the following query:

SELECT Name,startdate,address from employee

And I got data like this:

Name  StartDate Address
John   01/01/2017   na

Whereas I want data like this:

Name      | John
------------------------
StartDate | 01/01/2017
Address   |  NA

How can I write the correct query to get the expected result?

Answer 1

The PIVOT & UNPIVOT operators are more pain than what they are worth. Unpivoting columns is much easier (and faster) using a cross apply...

IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL 
DROP TABLE #TestData;

CREATE TABLE #TestData (
    f_Name VARCHAR(10),
    StartDate DATE,
    Address VARCHAR(30)
    );
INSERT #TestData(f_Name, StartDate, Address)
VALUES ('Johm', '2017-01-01', 'n/a');

--=================================================

SELECT 
    cav.ColLabel, 
    cav.ColValue
FROM
    #TestData td
    CROSS APPLY ( VALUES 
        (1, 'Name', td.f_Name),
        (2, 'StartDate', CAST(td.StartDate AS VARCHAR(10))),
        (3, 'Address', td.Address)
        ) cav (SortVal, ColLabel, ColValue)
ORDER BY
    cav.SortVal;

Results...

ColLabel  ColValue
--------- ------------------------------
Name      Johm
StartDate 2017-01-01
Address   n/a