T-SQL – Copying & Transposing Data

I’m trying to copy data from one table to another, while transposing it and combining it into appropriate rows, with different columns in the second table.

First time posting. Yes this may seem simple to everyone here. I have tried for a couple hours to solve this. I do not have much support internally and have learned a great deal on this forum and managed to get so much accomplished with your other help examples. I appreciate any help with this.

Table 1 has the data in this format.

Type    Date  Value
--------------------
First   2019  1
First   2020  2
Second  2019  3
Second  2020  4

Table 2 already has the Date rows populated and columns created. It is waiting for the Values from Table 1 to be placed in the appropriate column/row.

Date  First Second
------------------
2019    1     3
2020    2     4

Answer

For an update, I might use two joins:

update  t2
    set first = tf.value,
        second = ts.value
    from table2 t2 left join
         table1 tf
         on t2.date = tf.date and tf.type = 'First' left join
         table1 ts
         on t2.date = ts.date and ts.type = 'Second'
    where tf.date is not null or ts.date is not null;

Advertisement

Answer