TSQL Pivot Switch

Question

I am trying to pivot the DataDescription rows into columns, and unpivot the Number* columns into rows aliased into a new column.

The desired columns would be:

Location, 2016-01-01, 2016-01-02, 2016-01-03, 2016-01-04, 2016-01-05,CountType

Below is the code to setup the scenario. Would this be done more cleanly in C# or TSQL? Any suggestions?

if (object_id('tempdb..#data') is not null)
    begin
        drop table #data
    end

    create table #data
    (
        DateDescription VARCHAR(50),
        Location VARCHAR(50),
        NumberOfVisits INT,
        NumberOfPositiveVisits INT,
        NumberOfNegativeVisits INT
    )

    insert into #data
    SELECT '2016-01-01', 'SiteA', 100, 80, 20
    union all 
    SELECT '2016-01-02', 'SiteA', 95, 81, 21
    union all 
    SELECT '2016-01-03', 'SiteA', 95, 81, 21
    union all 
    SELECT '2016-01-04', 'SiteA', 95, 81, 21
    union all 
    SELECT '2016-01-05', 'SiteA', 95, 81, 21
    union all 
    SELECT '2016-01-01', 'SiteB', 95, 81, 21
    union all 
    SELECT '2016-01-02', 'SiteB', 95, 81, 21
    union all 
    SELECT '2016-01-03', 'SiteB', 95, 81, 21
    union all 
    SELECT '2016-01-04', 'SiteB', 95, 81, 21
    union all 
    SELECT '2016-01-05', 'SiteB', 95, 81, 21

    select * from #data

Answer 1

Something like this will work:

SELECT
    Location
    , [2016-01-01]
    , [2016-01-02]
    , [2016-01-03]
    , [2016-01-04]
    , [2016-01-05]
    , CountType
FROM
    (
        SELECT
            DateDescription
            , Location
            , [Count]
            , CountType
        FROM
            (
                SELECT
                    DateDescription
                    , Location
                    , NumberOfVisits
                    , NumberOfPositiveVisits
                    , NumberOfNegativeVisits
                FROM #data
            ) Q
            UNPIVOT
            (
                [Count]
                FOR CountType IN ([NumberOfVisits], [NumberOfPositiveVisits], [NumberOfNegativeVisits])
            ) U
    ) Q
    PIVOT
    (
        MAX([Count])
        FOR DateDescription IN ([2016-01-01], [2016-01-02], [2016-01-03], [2016-01-04], [2016-01-05])
    ) P
ORDER BY
    Location
    , CountType DESC