Q:

Linear regression in R with data from Sql server

I want to import data from MS Sql Server and apply linear regression on the data in R. But i am not sure how i can manipulate the data from sql server so that i can do a regression. My table in sql server looks like this,
Pack    Cubes   Name    Sales
1001    1.2      A      10
1001    1.2      B      12
1002    0.9      A      8
1002    0.9      B      5
1002    0.9      C      12
1003    1.5      A      5
1003    1.5      C      10
1004    0.8      B      8
1004    0.8      C      10
1005    1.3      A      5
1005    1.3      B      8
1005    1.3      C      12

If i would manipulate the data in excel for a regression model it would looks like this,
Cubes   A   B   C
1.2    10   12  0
0.9    8    5   12
1.5    5    0   10
0.8    0    8   10
1.3    5    8   12

The A, B, C is my dependent variables and Cubes my independent variable. The Pack in my sql table is just a reference. My Sql connection to a DSN looks like this (which works perfectly),
library(RODBC)
myconn <- odbcConnect("sqlserver")
data <- sqlQuery(myconn,"select Cubes,Name,Sales from mytable")

With the regression i tried (which is wrong),
summary(data)
reg<-lm(Cubes~Sales,data)
summary(reg)

How can i manipulate the data from sql server as i would if i did it in excel?

A:

Try reshape or the reshape package:
wide <- reshape(data, v.names = "Sales", idvar = "Cubes",
            timevar = "Name", direction = "wide")

