Copy Data from one table to another

[GeorgeAth]

Hello all.

I am rather inexperienced at SQL and I am facing a problem for which I would appreciate some help:

I have a table A which looks like:

ID User Item Rating
-------------------------------------
1 User1 Item1 SomeRating1
2 User1 Item2 SomeRating2
3 User2 Item1 SomeRating3

etc...The users can have ratings for more than one items and each item can have ratings from more than one users.

I want to copy the ratings to another table B that will look like this:

User Item1 Item2 Item3 ... ItemN
-----------------------------------------------------------
User1 SomeRating1 SomeRating2
User2 SomeRating3
User3
...
UserN

Where the user-item pair "coordinates" will be updated with the corresponding Rating value from table A (if no rating exists for the pair, it will remain NULL).

Any insights on how I could do such a thing?

 

[GeorgeAth]

To add to my post: I am able to do it by using two nested loops (the outer iterating all items and the inner all users) but it is a case of "poor" programming (and most importantly too slow too).

I hope that there is an easier, more effective way to do it that I cant figure out....

 

[simian336]

I assume tableb exists, if not create it.

insert into tableb (select distinct User from Tablea)

update b
set b.Item1=a.rating
from tablea a
join tableb b
on a.user=b.user
where a.item='Item1'

update b
set b.Item2=a.rating
from tablea a
join tableb b
on a.user=b.user
where a.item='Item2'

update b
set b.Item3=a.rating
from tablea a
join tableb b
on a.user=b.user
where a.item='Item3'

Totally untested but should be close.

Simi

 

[simian336]

Sorry, I think that insert would be

insert into tableb (user) select distinct User from Tablea

Simi

 

[kjv1611]

If you're doing this for one report, I'd think this is a good route to go, but for main dataset, I'd suggest sticking with your current format.

However, if you really want to go there, what you need to go with (I think) is a CrossTab Query. Take a look at that via the helpfiles.

 

[simian336]

True, I was busy looking at the trees and did not see the forest.

: )

Simi

 

[GeorgeAth]

Thank you for your interest.

Just for the record I post my "unorthodox" solution to my problem. I passed the database to Matlab creating a matrix with the triplets User-Item-Ratings (conveniently enough all three were Integers e.g User1 was represented as "1") and produced my desired table in just 2 seconds for my 100k+ rows long dataset using matrix operations.

As the database is a part of a longer data-mining project I did not really care about the existence of an SQL table with the data (I can as easily work with a flat file) so the result did fit my needs.

I know that my approach is not generally applicable but Internet is a big world, maybe some day a fellow data-miner reaches this thread looking for the same thing.