0

I have a sheet have data like this:

  • VendorName Description FuelType
  • Avery    Wood     Wood
  • Beta    LP Gas    LP Gas
  • Clever  Oil,Kerosene,LP Gas   Oil
  • Clever  Oil,Kerosene,LP Gas  Kerosene
  • Clever  Oil,Kerosene,LP Gas  LP Gas

But now, I need to convert them like this

this one called vendor table

  • VendorName:
  • Avery
  • Beta
  • Clever

this one called vendor fueltype table

  • VendorName   fueltype
  • Avery      Wood
  • Beta     LP Gas
  • Clever     Oil
  • Clever     Kerosene
  • Clever      LP Gas

I feel I can directly use the select from insert into, to move the sheet I have to the fueltype table, but somehow I couldn't think of a good way to make all the record in the second table. I am thinking there is something that if there are duplicated name in table, select the first one, or something like that.

Can anyone give some advice?

enter image description here

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Zecheng Li
  • 37
  • 5
  • Possible duplicate of: [Turning a Comma Separated string into individual rows](https://stackoverflow.com/questions/5493510/turning-a-comma-separated-string-into-individual-rows). – Tim Biegeleisen Feb 11 '19 at 01:12
  • @TimBiegeleisen Nope, sir. It is not. Like I say, I know how to deal with from the first one to the third one, but I don't know how to deal with from first one to the second one. – Zecheng Li Feb 11 '19 at 01:14
  • 1
    The vendor table is just a distinct select on the first one. – Tim Biegeleisen Feb 11 '19 at 01:27

2 Answers2

0

Based on what you have given us, the following should work:

CREATE TABLE #Original (
     VendorName     varchar(100)
    ,[Description]  varchar(100)
    ,FuelType       varchar(100)
    )

CREATE TABLE Vendor (
    VendorName  varchar(100)
    )

CREATE TABLE VendorFuelType(
     VendorName varchar(100)
    ,FuelType   varchar(100)
    )

INSERT INTO #Original (VendorName, [Description], FuelType)
    VALUES   ('Avery', 'Wood', 'Wood')
            ,('Beta', 'LP Gas', 'LP Gas')
            ,('Clever', 'Oil,Kerosene,LP Gas', 'Oil')
            ,('Clever', 'Oil,Kerosene,LP Gas', 'Kerosene')
            ,('Clever', 'Oil,Kerosene,LP Gas', 'lP Gas')

INSERT INTO Vendor(VendorName)
    SELECT  DISTINCT VendorName 
    FROM    #Original


INSERT INTO VendorFuelType(VendorName, FuelType)
    SELECT  VendorName, FuelType
    FROM    #Original

Although the example hasn't set up the Primary / Foreign Keys it is presumed that they would exist, hence the reason the Vendor table is populated first, then the VendorFuelType table.

As noted in the comments, the Vendor table is just a SELECT DISTINCT on the VendorName.

Mathew Paxinos
  • 944
  • 7
  • 16
0

For MySQL you can Simply Use following query to insert into another table:

INSERT INTO Vendor(VendorName) SELECT VendorName FROM OriginalTable;

INSERT INTO VendorFuelType(VendorName, FuelType) SELECT VendorName, FuelType 
FROM OriginalTable;

This can help to take duplicate entries also. Other Way is to use DB Cursor which also gives you same result. Here's a linkFor Cursor.
For SQL Server you can use query above but for Cursor there are some changes

Shwetali
  • 31
  • 4