Dynamic Update a created temp table? basics?

Question

I have Table-A that has "x" number of rows. (For this example there are 8 rows)

I create Table-C with "x" number of columns via use of a cursor. (Making this dynamic; should more rows be added to Table-A, then more columns are created in Table-C)

Table-C (x = 1 thru 8)
   UserID  1     2     3     4     5     6     7     8

After creating Table-C, rows are inserted into Table-C based on userIDs passed back from the webpage input box. (this is done dynamic)

Table-C after inserts:
   UserID  1     2     3     4     5     6     7     8  
   57      null  null  null  null  null  null  null  null  
   74      null  null  null  null  null  null  null  null  

Now I wish to perform an UPDATE
Table-B contains data where column "x-column" is associating a UserID to a column in the created Table-C

Table-B:  
UserID  x-column  
34      2  
34      3  
57      2  
57      3  
57      8  
74      2  
74      4  
74      5  
74      7  
74      8  
93      2  
93      4  

So end result is to dynamically UPDATE Table-C with a 1 where Table-B.UserID = Table-C.column_heading

Table-C after update should look like this:  
   UserID  1     2     3     4     5     6     7     8  
   57      null  1     1     null  null  null  null  1  
   74      null  1     null  1     1     null  1     1  

I am hard pressed on figuring out the syntax for building an UPDATE loop that will take care of this. Do I need to use a cursor?

I am pretty sure this isn't rocket science, I'm still learning!

Answer 1

Without seeing your entire process, here is my suggestion about how you can perform this. Part of the problem is the structure of tablec so to do this you will first, use the UNPIVOT function to unpivot the tablec and insert into a temp table:

select userid,
  value,
  col
into #temp
from tablec
unpivot
(
  value for col in ([1], [2], [3], [4], [5], [6], [7], [8])
) u

Second, update the data in the temp table using tableb:

update t
set value = b.cnt
from #temp t
left join
(
  select count(x_column) cnt, x_column, userid
  from tableb
  group by x_column, userid
) b
  on t.userid = b.userid 
  and t.col = b.x_column

Finally, use the PIVOT function to transform the data back to the format that you want:

select *
from
(
  select userid, value, col
  from #temp
) x
pivot
(
  max(value)
  for col in ([1], [2], [3], [4], [5], [6], [7], [8])
) p

See SQL Fiddle with Demo.

A few suggestions would be to see if you need to actually create tablec at the point you are, if not, then you can create it when you perform the PIVOT, then the data is in the final result that you need. However, you can leave the data in the format of rows and then just return it as columns as I did.

If you have an unknown number of columns that are being transformed, then this can be performed with dynamic sql.