Home TSQL Pivot Switch
Reply: 0

TSQL Pivot Switch

user1353
1#
user1353 Published in July 17, 2018, 7:55 am

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
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.32739 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO