CODEWITHSUNDEEP
sqlsql-server
mdt
mdt

Reputation: 139

Insert columns into rows for each ID

I have two tables:

table 1:

    ID       name 
---------  -----------
    1         john
    2         salma
    3         tony

table2:

    ID         Tasks      amount
---------  -----------  ----------
    1         write         2
    1         memorize      3
    1         read          6
    1         sing         NULL
    2         write         1
    2         memorize     NULL
    2         read          5
    2         sing         NULL
    3         write        NULL
    3         memorize      8
    3         read          2
    3         sing         NULL

I want to insert new columns in table1 for each task mentioned in table2.

Table1:

    ID        name       write    memorize    read    sing
---------  ----------- --------  ---------  ------- --------
    1         john        2          3         6      NULL
    2         salma       1         NULL       5      NULL
    3         tony       NULL        8         2      NULL

I can do the insert in Table1 for one ID at a time, but not for all of them. Thanks in advance!

Upvotes: 0

Views: 1609

Answers (1)

mdt
mdt

Reputation: 139

First, I inserted the row values in a temporary table as columns using pivot:

 select * into #Results 
        from
        (
          select ID,Tasks,amount
          from #Table2

        ) tb2
    pivot
    (
      max(amount)
      for ID in ([1], [2], [3])
    ) as piv

Then, I did an inner join with Table1:

select * from Table1 tb1 inner join #Results r on r.ID =tb1.ID

Thanks @JamesL for the seggustion to use pivot!

Upvotes: 1

Related Questions