CODEWITHSUNDEEP
sqlrjoin
sand
sand

Reputation: 137

SQL/R: Joining 2 tables such that the attribute values form 2nd table become attributes of the first

When joining 2 tables, I want to transform record values as attributes. (i.e. I have these two tables):

Customer table:

|CustomerId| Name|

|1         |Abc  |

|2         |Xyz  |

and I have Food preference table:

|CustomerId|Preference |

|1         |Continental|

|1         |Italian    |

|2         |Italian    |

|2         |Indian     |

|2         |Chinese    |

Now I want to join the 2 tables into the following table:

|CustomerId|Name|Food-Continental|Food-Italian|Food-Indian|Food-Chinese|

|1         |Abc |TRUE            |TRUE        |FALSE      |FALSE       |

|1         |Xyz |FALSE           |TRUE        |TRUE       |TRUE        |

Is there a script in R or an SQL query which will help me achieve this easily? Any help is appreciated :)

Upvotes: 0

Views: 61

Answers (2)

Rigel1121
Rigel1121

Reputation: 2016

I've got what you're asking for using MSSQL. Try it.

SELECT 
    CustomerID,
    Name,
    CASE WHEN Continental='Continental' Then 'TRUE' ELSE 'FALSE' END AS [Food-Continental],
    CASE WHEN Italian='Italian' Then 'TRUE' ELSE 'FALSE' END AS [Food-Italian],
    CASE WHEN Indian='Indian' Then 'TRUE' ELSE 'FALSE' END [Food-Indian],
    CASE WHEN Chinese='Chinese' Then 'TRUE' ELSE 'FALSE' END [Food-Chinese]
    FROM (
SELECT 
     A.CustomerID,Name,Preference
FROM customer A 
     LEFT JOIN 
     food B on A.CustomerID=B.CustomerID) AS C

     PIVOT(
            MIN(Preference) FOR Preference IN ([Continental],[Italian],[Indian],[Chinese])
          )AS PVT

Upvotes: 0

MrFlick
MrFlick

Reputation: 206187

You could use dplyr and tidyr. For example, with this input

a<-read.table(text="CustomerId Name
  1 Abc
  2 Xyz", header=T)
b<-read.table(text="CustomerId Preference
  1 Continental
  1 Italian
  2 Italian
  2 Indian
  2 Chinese", header=T)

then you can do

library(dplyr)
library(tidyr)
inner_join(a, b) %>% mutate(val=TRUE) %>% 
    spread(Preference, val, fill=FALSE)

Which produces

  CustomerId Name Chinese Continental Indian Italian
1          1  Abc   FALSE        TRUE  FALSE    TRUE
2          2  Xyz    TRUE       FALSE   TRUE    TRUE

You can change the column names and order if you really want, but this should give you the data.

The transformation is done in two steps. First, we do a standard merge it the data. This results in a dataset in the "long" format. We then use spread to reshape the long data into wide format.

Upvotes: 1

Related Questions