Search code examples
sqlpermutation

Permutations of 7, replacing one field at a time

enter image description here

I don't even know how to name this topic.

Here's what I'm trying to do. I have sets of data that I have to process and they might have a few versions of the same field.

For example, I have a data set that comes in with the following columns:

FacilityName
FacilityAlias
TIN
TIN2
NPI
NPI2
NPI3
MailingAddress
MailingCity, State (they're given to me as 2 different fields but for my needs they're counted as 1)
MailingZIP
BillingAddress
Billing City, State
BillingZIP

my final dataset will only have the columns below

FacilityName
TIN
NPI
Address
CityState
ZIP

but I have to make sure I have a row for each possible combination of the fields, so I have to create the following queries:

INSERT INTO MATCH ( FacilityName, TIN, NPI, Address, CityState, ZIP) SELECT FacilityName, TIN, NPI. BillingAddress, BillingCityState, BillingZIP FROM MATCHPREP;

INSERT INTO MATCH ( FacilityName, TIN, NPI, Address, CityState, ZIP) SELECT FacilityAlias, TIN, NPI. BillingAddress, BillingCityState, BillingZIP FROM MATCHPREP;

INSERT INTO MATCH ( FacilityName, TIN, NPI, Address, CityState, ZIP) SELECT FacilityName, TIN, NPI. MailingAddress, MailingCityState, MailingZIP FROM MATCHPREP;

INSERT INTO MATCH ( FacilityName, TIN, NPI, Address, CityState, ZIP) SELECT FacilityName, TIN2, NPI. BillingAddress, BillingCityState, BillingZIP FROM MATCHPREP;

INSERT INTO MATCH ( FacilityName, TIN, NPI, Address, CityState, ZIP) SELECT FacilityAlias, TIN2, NPI. BillingAddress, BillingCityState, BillingZIP FROM MATCHPREP;

INSERT INTO MATCH ( FacilityName, TIN, NPI, Address, CityState, ZIP) SELECT FacilityName, TIN2, NPI. MailingAddress, MailingCityState, MailingZIP FROM MATCHPREP;

and so on.

How is this done?

Solution

  • It's quite difficult to give the exact answer as I'm not 100% sure why you're doing this.

    But an easy way to use sql to return a set of multiple permutation is to create a query that returns a cartisian product.

    e.g. select a,b,c from (select 1 a union select 2 a union select 3 a) (select 10 b union select 20 b union select 30 b) (select 100 c union select 200 union select 300 c)

    notice no joins. the sub queries could be distinct queries from the matchprep table. in this example the results would look like

    1,10,100
1,10,300
1,10,300
...
...
3,30,100
3,30,200
3,30,300

    You could then insert the return data set.

    http://en.wikipedia.org/wiki/Cartesian_product

    EDIT:

    This has to be it :)

    select  
name, tin, npi, address, state, zip
from (select distinct name from (select FacilityName as name from matchprep 
    union select FacilityAlias as name from matchprep) f cross join
(select distinct tin (select TIN as tin from matchprep 
    union select TIN2 as tin from matchprep)) t cross join
 (select distinct NPI from matchprep) n cross join
 (select distinct address,state,zip from (select BillingAddress as address, BillingCityState as state, BillingZIP as zip from matchprep 
    union select MailingAddress as address, MailingCityState as state, MailingZIP as zip from matchprep)) a

    Based on your example inserts, Mailing address details and Billing address details are always distinct from each other.

    FacilityName and Alias will be one permutation. Tin and Tin2 will be another You'll get a cartesian product of for each NPI.