Update SQL Server table values in one table based upon values in another table that relate to the column names in the first

Question

I've tried everything I can think off to update a table with values from another table, with values that relate to the names of the columns in the first table. If anyone has any ideas how I can do it I would appreciate it.

I have a temporary table in a stored procedure similar to this...

ProductId Y2010 Y2011 Y2012 Y2013 Y2014 Y2015 Y2016 Y2017 Y2018
--------- ----- ----- ----- ----- ----- ----- ----- ----- -----
    1       0     0     0     0     0     0     0     0     0
    2       0     0     0     0     0     0     0     0     0
    3       0     0     0     0     0     0     0     0     0

The table I would like to use to update the temporary table is similar to this...

ProductId FromYear ToYear
--------- -------- ------
    1       2010     2012
    2       2011     2014
    1       2015     2016
    3       2012     2017
    2       2017     2018

and the outcome I am looking for is...

ProductId Y2010 Y2011 Y2012 Y2013 Y2014 Y2015 Y2016 Y2017 Y2018
--------- ----- ----- ----- ----- ----- ----- ----- ----- -----
    1       1     1     1     0     0     1     1     0     0
    2       0     1     1     1     1     0     0     1     1
    3       0     0     1     1     1     1     1     1     0

Answer 1

Just use conditional aggregation:

select productid,
       max(case when 2010 between fromyear and toyear then 1 else 0 end) as Y2010,
       max(case when 2011 between fromyear and toyear then 1 else 0 end) as Y2011,
       . . .   -- fill in with the rest of the years
       max(case when 2018 between fromyear and toyear then 1 else 0 end) as Y2010,
from t
group by productid;

I am unclear whether you want to update an existing table or just produce this format. If you want an update, it is easy to join this version in and update the original table.

EDIT:

To update a table, you can do:

with newvalues as (
      select productid,
             max(case when 2010 between fromyear and toyear then 1 else 0 end) as Y2010,
             max(case when 2011 between fromyear and toyear then 1 else 0 end) as Y2011,
             . . .   -- fill in with the rest of the years
             max(case when 2018 between fromyear and toyear then 1 else 0 end) as Y2010,
      from t
      group by productid
     )
update
    set Y2010 = nv.Y2010,
        Y2011 = nv.Y2011,
        Y2012 = nv.Y2012,
        Y2013 = nv.Y2013,
        Y2014 = nv.Y2014,
        Y2015 = nv.Y2015,
        Y2016 = nv.Y2016,
        Y2017 = nv.Y2017,
        Y2018 = nv.Y2018
from existingtable et join
     newvalues nv
     on et.productid = nv.productid;

Answer 2

I would approach this by putting your second table into the same format as the first table, which can be done by joining to a list of years, then pivoting the results

CREATE TABLE #T (ProductId INT, FromYear INT, ToYear INT);
INSERT INTO #T (ProductId, FromYear, ToYear)
VALUES (1, 2010, 2012), (2, 2011, 2014), (1, 2015, 2016), (3, 2012, 2017), (2, 2017, 2018);

WITH Years AS 
(   SELECT  Year
    FROM    (VALUES (2010), (2011), (2012), (2013), (2014), (2015), (2016), (2017), (2018)) t (Year)
), AllYears AS
(   -- JOIN TO EXPAND THE RANGE INTO ONE ROW PER YEAR
    SELECT  t.ProductID, Year = CONCAT('Y', y.Year), Value = 1
    FROM    #T AS t
            INNER JOIN Years AS y
                ON y.Year >= t.FromYear
                AND y.Year <= t.ToYear
)
SELECT  *
FROM    AllYears AS y
        PIVOT
        (   MAX(Value)
            FOR Year IN (Y2010, Y2011, Y2012, Y2013, Y2014, Y2015, Y2016, Y2017, Y2018) 
        ) AS pvt;

This outputs your required results, so the update may not be necessary, but if it is, you can simply join back to your original temporary table:

CREATE TABLE #T2 (ProductId INT, Y2010 INT, Y2011 INT, Y2012 INT, Y2013 INT, Y2014 INT, Y2015 INT, Y2016 INT, Y2017 INT, Y2018 INT);

INSERT INTO #T2
    (ProductId, Y2010, Y2011, Y2012, Y2013, Y2014, Y2015, Y2016, Y2017, Y2018)
VALUES
    (1, 0, 0, 0, 0, 0, 0, 0, 0, 0),
    (2, 0, 0, 0, 0, 0, 0, 0, 0, 0),
    (3, 0, 0, 0, 0, 0, 0, 0, 0, 0);

WITH Years AS 
(   SELECT  Year
    FROM    (VALUES (2010), (2011), (2012), (2013), (2014), (2015), (2016), (2017), (2018)) t (Year)
), AllYears AS
(   -- JOIN TO EXPAND THE RANGE INTO ONE ROW PER YEAR
    SELECT  t.ProductID, Year = CONCAT('Y', y.Year), Value = 1
    FROM    #T AS t
            INNER JOIN Years AS y
                ON y.Year >= t.FromYear
                AND y.Year <= t.ToYear
)
UPDATE  tr
SET     Y2010 = pvt.Y2010,
        Y2011 = pvt.Y2011,
        Y2012 = pvt.Y2012,
        Y2013 = pvt.Y2013,
        Y2014 = pvt.Y2014,
        Y2015 = pvt.Y2015,
        Y2016 = pvt.Y2016,
        Y2017 = pvt.Y2017,
        Y2018 = pvt.Y2018
FROM    AllYears AS y
        PIVOT
        (   MAX(Value)
            FOR Year IN (Y2010, Y2011, Y2012, Y2013, Y2014, Y2015, Y2016, Y2017, Y2018) 
        ) AS pvt
        INNER JOIN #T2 AS t
            ON t.ProductID = pvt.ProductID;

SELECT  *
FROM    #T2;

With all that being said, I would largely advocate against this approach. The results from the query that expands your ranges into one row per year are likely to far more consumable than the pivoted results, i.e.

WITH Years AS 
(   SELECT  Year
    FROM    (VALUES (2010), (2011), (2012), (2013), (2014), (2015), (2016), (2017), (2018)) t (Year)
)
SELECT  t.ProductID, y.Year
FROM    #T AS t
        INNER JOIN Years AS y
            ON y.Year >= t.FromYear
            AND y.Year <= t.ToYear;

To produce:

ProductID   Year
-----------------
1           2010
1           2011
1           2012
......

Doing the pivot in your presentation layer will most likely be trivial.

The benefit of this approach is that it is very straightforward to generate a list of years dynamically, so you won't need to alter your procedure to add/remove years.