Home TSQL Pivot Switch
Reply: 2

TSQL Pivot Switch

jreed350z
1#
jreed350z Published in 2017-12-07 13:36:27Z

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
Chris Mack
2#
Chris Mack Reply to 2017-12-07 16:21:43Z

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
Artem
3#
Artem Reply to 2017-12-07 16:11:20Z

If you are looking for T-Sql solution, you'd probably want to dig into Pivot capabilitites. Not sure what do you expect from the CountType column, but grouping might be done in the following way:

SELECT Location
      ,[2016-01-01]
      ,[2016-01-02]
      ,[2016-01-03]
      ,[2016-01-04]
      ,[2016-01-05]
      ,[Type]
FROM (
    SELECT [DateDescription]
          ,Location
          ,NumberOfVisits as visits
          ,'Total' AS [Type]
    FROM #data) AS Source
PIVOT
(
    SUM(visits)
    FOR
    DateDescription IN
    (
       [2016-01-01]
      ,[2016-01-02]
      ,[2016-01-03]
      ,[2016-01-04]
      ,[2016-01-05]
    )
) AS PivotTable

UNION    

SELECT Location
      ,[2016-01-01]
      ,[2016-01-02]
      ,[2016-01-03]
      ,[2016-01-04]
      ,[2016-01-05]
      ,[Type]
FROM (
    SELECT [DateDescription]
          ,Location
          ,NumberOfPositiveVisits as visits
          ,'Positive' AS [Type]
    FROM #data) AS Source
PIVOT
(
    SUM(visits)
    FOR
    DateDescription IN
    (
       [2016-01-01]
      ,[2016-01-02]
      ,[2016-01-03]
      ,[2016-01-04]
      ,[2016-01-05]
    )
) AS PivotTable

UNION 

    SELECT Location
      ,[2016-01-01]
      ,[2016-01-02]
      ,[2016-01-03]
      ,[2016-01-04]
      ,[2016-01-05]
      ,[Type]
FROM (
    SELECT [DateDescription]
          ,Location
          ,NumberOfNegativeVisits as visits
          ,'Negative' AS [Type]
    FROM #data) AS Source
PIVOT
(
    SUM(visits)
    FOR
    DateDescription IN
    (
       [2016-01-01]
      ,[2016-01-02]
      ,[2016-01-03]
      ,[2016-01-04]
      ,[2016-01-05]
    )
) AS PivotTable;
You need to login account before you can post.

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

© 2016 Powered by mzan.com design MATCHINFO