How can I select a distinct combination of values across 3 or 4 columns in SQL Server

Question

I inherited a non-normalized table in SQL Server, with 15 columns of purchase records.

| Index |TransNo| Fund | Cost | City | Zip | Bank | Risk_YesNo |......

| 1| 1| Green| 34| NYC | Zip | Sunt |

| 2| 12| Party| 345.23| NYC | Zip | BOA_BS |

| 3| 32| Edu | 43| Boston | Zip | BOA_BS |

| 4| 53| Books| 56| Atl | Zip | citi |

| 5| 422| Groc| 12315| Atl | Zip | Pink |

1. I only need to extract 3 Distinct Combination of columns, and ignore the others like index and trans No and insert them into the same table (with the rest of columns as nulls

2. In planning for next phase, I want to 2 Normalize - I also need to insert each distinct value into its own table

| Fund | City | Bank |

| Green | NYC | Sunt | // all distinct combinations of green with city and bank

| Green | NYC | BOA_BS |

| Edu | NYC | Sunt | // all distinct combinations of Edu with city and bank

| Edu | NYC | BOA_BS |

Answer 1

Use insert into .. select from construct with distinct to get only distinct records like below

insert into mytable
select distinct Fund, City, Bank, null,null,null,....,null
from mytable

EDIT:

Per your comment below

1) how can I fill a default value for a column Risk_YesNo=Y

Just include that in the SELECT query [See example below]

2) how can I fill a random cost value for e.g. for a column [Cost]

You can include this as well in the SELECT part of query. If you are using SQL Server 2008 and above you can use RAND() function to achieve the same.

RAND() function returns a float type result. So, in case your Cost column if of type Integer, you will have to cast it explicitly saying CAST(RAND() as int) [See example below]

With that your query will become

insert into mytable(Fund, City, Bank, Risk_YesNo, Cost, ....)
select distinct Fund, City, Bank, 'Y', RAND(), null,null,null,....,null
from mytable