2

I'm kind of new at SQL, so please bear with me :) I have a population of people and about 60 or so possible characteristics, let's call them x1,x2,...,x60. For each person, one, many or none of these characteristics may apply. This information is stored in a table:

TABLE1:
ID  C
1   x1
1   x2
1   x6
2   x3
2   x7
3   x2
4   x2   
4   x5
4   x6
4   x8
...

I want to represent this information using 60 columns of bits, where 1 means the characteristic is present, and 0 means it is not.

TABLE2:
ID  x1  x2  x3  x4  x5  x6 x7  x8 ...
1   1   1   0   0   0   1  0   0
2   0   0   1   0   0   0  1   0
3   0   1   0   0   0   0  0   0
4   0   1   0   0   1   1  0   1

it's really tedious to update the columns one by one, ie repeating

update table2 set table2.x1 = 1
from  table1 
where table1.C = 'x1'

60 times for each of the xi's. And it runs really slow.

I was thinking of doing something like

update table2 
set table2.x1 = case table1.C when 'x1' then 1 else 0 end,
set table2.x2 = case table1.C when 'x2' then 1 else 0 end,
...
from table1

which is much faster, but it doesn't work in the case where an individual has multiple characteristics, all except the last are overwritten.

Anyway, if someone has any suggestions that would be great! I feel like this kind of task should be pretty common and hopefully someone has an ingenious way of going about it...

Thanks!

mmsk
  • 21
  • 1

1 Answers1

3

What you have there is a standard many-to-many relationship. You want a table of people, a table of possible characteristics, and then a middle connector table. The connector table contains two columns. One is the primary key of the person and the second is the primary key of the characteristic.

JOTN
  • 6,120
  • 2
  • 26
  • 31
  • Hi Jotn, thank you so much for your response. From what I understand, using a connector table can make queries faster using an index. However, I still need all the columns of binaries recording each individual's characteristics. This format is necessary for the statistical software I'm using later. Do you know if there is some way to update them without copy/pasting the update statement? Thanks! – mmsk Nov 15 '10 at 15:44