UNPIVOT Holiday Hours

This article will delve into the process of unpivoting a table in SQL Server, which is a common task when working with data that needs to be transformed from a wide format to a long format. We’ll explore how to achieve this using Dynamic SQL and a Table-Valued Function.

Understanding Wide and Long Formats

When working with tables, we often encounter data that is represented in either a wide or long format. A wide format table has multiple rows, each containing a separate value for a particular column. On the other hand, a long format table has only one row per record, with multiple columns representing different values.

In this case, our problem is to unpivot a table from its wide format to a long format. The desired output will have two columns: DATE and TIME, each containing a value for a specific date.

Sample Data

To illustrate the concept, let’s create a sample table with holiday hours data.

CREATE TABLE #HOURS (
    LOCATION_ID INT,
    DATE1 VARCHAR(255),
    TIMES1 VARCHAR(255),
    DATE2 VARCHAR(255),
    TIMES2 VARCHAR(255)
);

INSERT INTO #HOURS VALUES ('123456', '2020-12-12', '10:00AM-09:00PM', '2020-12-19', '10:00AM-09:00PM');

This table has multiple rows, each representing a specific date and time for a particular location. We want to unpivot this data so that we have only one row per location, with two columns: DATE and TIME.

Initial Attempt using UNPIVOT

Our first attempt at using the UNPIVOT function is as follows:

SELECT * 
FROM   (SELECT location_id, 
               [date1], 
               [times1], 
               [date2] 
        FROM   #hours) AS cp 
       UNPIVOT ( pivotvalues 
               FOR pivvalues IN ([Date1], 
                                 [date2], 
                                 [times1]) ) AS up1;

However, this approach doesn’t quite work as expected. We’ll explore why and then provide a corrected solution.

Why the Initial Attempt Fails

The UNPIVOT function in SQL Server can only be used with aggregate functions or when the unpivoted columns are of the same data type. In our case, we have multiple columns with different data types (strings). This is where the issue arises: the UNPIVOT function cannot handle this scenario.

Dynamic Approach without Dynamic SQL

To overcome this limitation, we can use a dynamic approach that avoids using Dynamic SQL. Instead, we’ll utilize a Table-Valued Function (TVF) to achieve the desired result.

TVF Implementation

First, let’s create a TVF called tvf-XML-UnPivot-Row:

CREATE FUNCTION [dbo].[tvf-XML-UnPivot-Row] (@XML xml)
Returns Table 
As
Return (
        Select Item  = xAttr.value('local-name(.)', 'varchar(100)')
              ,Value = xAttr.value('.','varchar(max)')
         From  @XML.nodes('//@*') xNode(xAttr)
);

This TVF takes an XML string as input and returns a table with the unpivoted columns.

Main Query

Now, let’s use this TVF in our main query:

Select Location_ID
      ,Date  = max(case when [Item] like 'DATE%' then Value end)
      ,Time  = max(case when [Item] like 'TIME%' then Value end)
 From (
        select A.Location_ID
              ,Grp = replace(replace([Item],'DATE',''),'TIMES','')
              ,B.*
         from #hours  A
         Cross Apply [dbo].[tvf-XML-UnPivot-Row]( (Select A.* for XML RAW) ) B
         Where [Item] not in ('LOCATION_ID')
      ) A
 Group By Location_ID,Grp;

This query uses the TVF to unpivot the data and then groups the results by Location_ID and Grp.

Result

The final result will be a table with two columns: DATE and TIME, each containing a value for a specific date:

+---------+-------+-------+
| Location_ID | Date  | Time  |
+---------+-------+-------+
| 123456   | 2020-12-12 | 10:00AM-09:00PM |
| 123456   | 2020-12-19 | 10:00AM-09:00PM |
+---------+-------+-------+

This is the desired output, with only one row per location and two columns representing different values.

Conclusion

In this article, we explored how to unpivot a table in SQL Server using a dynamic approach without Dynamic SQL. We created a Table-Valued Function (TVF) to achieve this goal and then used it in our main query. The result was a table with the desired output format, which is useful when working with data that needs to be transformed from wide to long formats.

Last modified on 2023-11-04