Unpivot or Cross Apply?

Question

I have data in a wide table that has the year as part of the column name and 6 data fields that I need to unpivot.

SELECT TOP (1000) [id]
      ,[created_at]
      ,[country_territory]
      ,[code]
      ,[estimate_1996]
      ,[stderr_1996]
      ,[numsrc_1996]
      ,[rank_1996]
      ,[lower_1996]
      ,[upper_1996]
      ,[estimate_1998]
      ,[stderr_1998]
      ,[numsrc_1998]
      ,[rank_1998]
      ,[lower_1998]
      ,[upper_1998]
      ,[estimate_2000]
      ,[stderr_2000]
      ,[numsrc_2000]
      ,[rank_2000]
      ,[lower_2000]
      ,[upper_2000]
  FROM [mytable]

The table has more fields that my select or image are showing. I just selected a subset of the data. The years may grow (but the 6 data items won't or shouldn't). How can I easily use unpivot or cross-apply to return something like: id,code,year,estimate,stderr,numsrc,rank,lower,upper

While asking a question, you need to provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example): (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). All within the question, no images. — Yitzhak Khabinsky, May 19 '22 at 17:47
Why not permanently fix your design, rather than adding to the problem each year? — Thom A, May 19 '22 at 17:49
@Larnu because it's a problem I inherited and can't change the design. — Peter Kandra, May 19 '22 at 18:43
@YitzhakKhabinsky sorry if you think I didn't provide enough detail. I explained the output that I need. The sample data isn't really relevant. Assume SQL Server 2016 or higher. — Peter Kandra, May 19 '22 at 18:44

Rafal · Answer 1 · 2022-05-20T08:52:13.237

With unpivot you can do this but it will not account for new columns every year. Assuming adjusting query every year is not a big deal you can just write unpivot:

SELECT id,
       code, 
       substring(estimateYear, CHARINDEX('_',estimateYear,1)+1,4) as [year],  
       estimate,
       stderr,
       numsrc
FROM mytable p  
UNPIVOT  
   (estimate FOR estimateYear IN   
      (estimate_1996, estimate_1998, estimate_2000)) as estimate
UNPIVOT  
   (stderr FOR stderrYear IN   
      (stderr_1996, stderr_1998, stderr_2000)) as stderr
UNPIVOT  
   (numsrc FOR numsrcYear IN   
      (numsrc_1996, numsrc_1998, numsrc_2000)) as stderr
where substring(estimateYear, CHARINDEX('_',estimateYear,1)+1,4) = substring(stderrYear, CHARINDEX('_',stderrYear,1)+1,4)
and  substring(estimateYear, CHARINDEX('_',estimateYear,1)+1,4) = substring(numsrcYear, CHARINDEX('_',numsrcYear,1)+1,4);

this only gives you 3 first values but the pattern is pretty repetitive: add new UNPIVOT section for rest of the columns and new where condition at the end.

If you want this to be resilient to new columns being added at the expense of performance (that will be not that great with the unpivot itself) you can make this query dynamic:

declare @query as nvarchar(4000)
;with allcoumnswithyear as (
select SUBSTRING(name,1,charindex('_',name) - 1) valueName, SUBSTRING(name,charindex('_',name) + 1, 4) year, name
from sys.columns c 
where c.object_id = object_id('mytable') 
and name like '%[_]%' and name not in ('created_at','country_territory')
) , unpivotedValue as (
select 
    'UNPIVOT ('+valueName + ' FOR ' + valueName + 'Year IN ('+ STRING_AGG(name, ', ') +')) as ' + valueName unpivotPart, 
    'substring(estimateYear, CHARINDEX(''_'',estimateYear,1)+1,4) = substring('+valueName+'Year, CHARINDEX(''_'','+valueName+'Year,1)+1,4)' wherePart, 
    valueName
from allcoumnswithyear 
group by valueName)
select 
        @query='select id, code, substring(estimateYear, CHARINDEX(''_'',estimateYear,1)+1,4) as [year],'
        + string_agg(valueName, ', ') 
        + ' FROM mytable p '
        + string_agg(unpivotPart, ' ') 
        + ' where '
        + string_agg(wherePart, ' and ') 
from unpivotedValue

exec sp_executesql @query

basically this code writes the select statement from before each time it is executed and than executes the query.

STRING_AGG was introduced in sql server 2017 if you need this in older version you can substitute that with stuff and xml:

SELECT
  STUFF(
    (SELECT ',' + name AS [text()]
        from sys.columns c 
        where c.object_id = object_id('mytable') 
     FOR XML PATH('')), 1, 1, NULL) AS aggregatedtablenames;

@PeterKandra please mark answer as accepted if you are satisfied — Rafal, May 19 '22 at 19:35
*"Cross apply will not help with this"* it would when combined with a `VALUES` table construct, which is what the OP means. Many prefer the method over the restrictive `UNPIVOT` operator. — Thom A, May 19 '22 at 20:07
@Larnu please post answer with that solution I would like to know how to do that. — Rafal, May 19 '22 at 21:21
as per example 4 here https://stackoverflow.com/questions/9275132/real-life-example-when-to-use-outer-cross-apply-in-sql/9275865#9275865 — Martin Smith, May 20 '22 at 08:11

Unpivot or Cross Apply?

1 Answers1