You might consider using a composite key "1963 CAN ABB" when you design
your data structure.
Igor Tandetnik wrote:
> Vincent Arel <vincent.a...@gmail.com>
> wrote:
>
>> I'm very, very new to SQLite, and would appreciate any help I can get.
>>
>> I have 3 long vectors that look like this:
>> {"ALB","CAN", "DZA",...}
>> {"ALB","CAN", "DZA",...}
>> {"1961","1962", "1963",...}
>>
>> And I want to create a table that looks like this:
>>
>> ID Var1 Var2 Var3
>> 1 ALB CAN 1961
>> 1 ALB CAN 1962
>> 1 ALB CAN 1963
>> 2 ALB DZA 1961
>> 2 ALB DZA 1962
>> 2 ALB DZA 1963
>> 3 CAN ALB 1961
>> 3 CAN ALB 1962
>> 3 CAN ALB 1963
>> 4 CAN DZA 1961
>> 4 CAN DZA 1962
>> 4 CAN DZA 1963
>> 5 DZA ALB 1961
>> 5 DZA ALB 1962
>> 5 DZA ALB 1963
>> 6 DZA CAN 1961
>> 6 DZA CAN 1962
>> 6 DZA CAN 1963
>>
>> In short, I need to include every possible pair of Var1/Var2 values
>> (where Var1/Var2 != Var2/Var1. I want to keep permutations.). For
>> each of these pairs, I need to create separate rows for each
>> different value of Var3. I also need to drop rows where Var1 == Var2.
>> Finally, I would like to generate a unique ID number for each
>> Var1/Var2 pair.
>>
>
> What exactly do you mean when you say you "have vectors"? Are you
> writing a program in some language that has a notion of a vector? In
> this case, I'm pretty sure this language also supports loops iterating
> over those vectors, as well as incrementing integers.
>
> The easiest solution would be a set of nested loops iterating over those
> vectors. Inside the most nested loop, you would have all the data
> necessary to generate one record in the database, at which point you can
> run a regular vanilla INSERT statement using SQLite API.
>
> Igor Tandetnik
>
>
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
