I've been asked to create a query transforming data from something like the 1st image, with standard rows/columns

Year       Company     Completed        DayofWeek    Hour        Country        
---------------------------------------------------------------------
2022          A            Y             Mon           12         France
2019          A            N             Tue           14         Germany
2022          A            Y             Thu           13          Italy
2021          B            N             Sat           16          France
2022          B            Y             Mon           14           Spain
2021          B            Y             Tue           12           France

To something like the below where a number of the field names are on 1 column and the field values are in another

       Company     Completed     Field Name    Field Value    Total        
---------------------------------------------------------------------
          A            Y          DayofWeek          Mon        50
          A            Y          DayofWeek          Tue        35
          A            N          DayofWeek          Mon        40
          A            N          Hour               16         55
          A            Y          Hour               12         40
          A            Y          Hour               14         30

In the actual query there are more columns being transformed. I'm currently using lots of union statements e.g.

select
Company, Completed, 'Day of Week' as 'Field Name', [Day_of_Week] as 'Field Values', count(*) as Total
from Table 1
where year=2022
Group by
Company, Completed, [Day_of_Week]

union all

select
Company, Completed, 'Hour' as 'Field Name', [Hour] as 'Field Values', count(*) as Total
from Table 1
where year =2022
Group by
Company, Completed, [Hour]

I've been asked to do it this way to output fewer rows and also as customer would like to be able to compare the completed % across all measures at once.

However I feel there could be a more efficient way of doing this to not have so many unions/not manually update each union if changes to script, but haven't found anything - some way of looping through the different columns to add to bottom.

CodePudding user response：

You can CONVERT your columns you want to unpivot in a subquery (assuming the data type of Hour, DayofWeek, Country do not match - they must match for the PIVOT/UNPIVOT functions to work) then perform the UNPIVOT function to rotate your columns to rows.

The newly created Field_Value column from the UNPIVOT function will contain your column values and the Field_Name will contain your column names (you can change the alias for these columns if you want).

You can then perform a GROUP BY to aggregate your data based on Company, Completed, and your newly created Field_Name and Field_Value columns in order to get a COUNT for your Total column.

SELECT 
      u.Company, 
      u.Completed, 
      u.Field_Name,
      u.Field_Value,
      COUNT(u.Field_Value) AS Total
    FROM 
        (SELECT 
             Company,
             Completed,
             CONVERT(VARCHAR(10), DayofWeek) AS DayofWeek,
             CONVERT(VARCHAR(10), Hour) AS Hour,
             CONVERT(VARCHAR(10), Country) AS Country
          FROM sample_table) pv
    UNPIVOT 
        (Field_Value FOR Field_Name IN (DayofWeek, Hour, Country)) u
    GROUP BY u.Company, u.Completed, u.Field_Name, u.Field_Value
    ORDER BY u.Company, u.Field_Name ASC

See Fiddle.

Result:

**Note that I changed the N flag to Y for the Company B Completed column to show the GROUP BY Total working.

Read more about PIVOT and UNPIVOT here from Microsoft: FROM - Using PIVOT and UNPIVOT