How to extract data from a table holding seller and listing information into a table with individual records

Hello experts!

I need some help with SQL in extracting data from one table into another. In the table transactions, I have the Seller, SellerID, Listing, ListingID, SoldPrice, Date. I would like to extract each instance of Seller and Listing into individual records in another table called individualtransactions.

I figured the best way to showcase what I would like to accomplish is with an example before and after. Is this possible with one SQL statement or would I need to run multiple statements? If it is, can an expert guide me on how to accomplish this?

Table name: transactions
Seller, SellerID, Listing, ListingID, SoldPrice, Date
John, 1, Bill, 2, 1900, 12/01/2017
Jane, 3, Becky, 4, 2400, 12/02/2017
Joe, 5, Jane, 1, 2100, 12/03/2017

Table name: individualtransactions
Name, Price, Role, Date
John, 1900, Seller, 12/01/2017
Bill, 1900, Listing, 12/01/2017
Jane, 2400, Seller, 12/02/2017
Becky, 2400, Listing, 12/02/2017
Joe, 2100, Seller, 12/03/2017
Jane, 2100, Listing, 12/03/2017
Daniel WilliamsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
Hi Daniel,
Could you please provide the output you need from the inputs you have given. Thanks
0
Pawan KumarDatabase ExpertCommented:
Please try this solution with tested output

--


CREATE TABLE Sellers
(
	  Seller VARCHAR(20)
	, SellerID INT
	, Listing VARCHAR(20)
	, ListingID INT
	, SoldPrice INT
	, [Date] DATE
)
GO

INSERT INTO Sellers VALUES
('John', 1, 'Bill', 2, 1900, '12/01/2017'),
('Jane', 3, 'Becky', 4, 2400, '12/02/2017'),
('Joe', 5, 'Jane', 1, 2100, '12/03/2017')
GO

CREATE TABLE individualtransactions
(
	  Name VARCHAR(20)
	, Price INT
	, [Role] VARCHAR(15)
	, [Date] DATE
)
GO

--

Open in new window


>> Is this possible with one SQL statement or would I need to run multiple statements? If it is, can an expert guide me on how to accomplish this?
Please use below solution

SOLUTION-

--


INSERT INTO individualtransactions
SELECT Seller,SoldPrice,'Seller'Role,[Date]
FROM Sellers
UNION ALL
SELECT Listing,SoldPrice,'Listing',[Date]
FROM Sellers

--

Open in new window


OUTPUT /*order by clause is just for display purpose here.*/

--

/*------------------------
SELECT * FROM individualtransactions
ORDER BY Date,Role DESC
------------------------*/
Name                 Price       Role            Date
-------------------- ----------- --------------- ----------
John                 1900        Seller          2017-12-01
Bill                 1900        Listing         2017-12-01
Jane                 2400        Seller          2017-12-02
Becky                2400        Listing         2017-12-02
Joe                  2100        Seller          2017-12-03
Jane                 2100        Listing         2017-12-03

(6 row(s) affected)



--

Open in new window

0
Pawan KumarDatabase ExpertCommented:
Another SOLUTION using CROSS APPLY

--

INSERT INTO individualtransactions
SELECT CASE WHEN Role = 'Listing' THEN u.Listing ELSE u.Seller END Seller , u.SoldPrice , [Role],u.[Date]
FROM
(
	SELECT 'Seller' Role UNION ALL 
	SELECT 'Listing'  
)p
CROSS APPLY 
( 
	SELECT * FROM Sellers s 
)u

--

Open in new window


OUTPUT

--

/*------------------------
SELECT * FROM individualtransactions
------------------------*/
Name                 Price       Role            Date
-------------------- ----------- --------------- ----------
John                 1900        Seller          2017-12-01
Bill                 1900        Listing         2017-12-01
Jane                 2400        Seller          2017-12-02
Becky                2400        Listing         2017-12-02
Joe                  2100        Seller          2017-12-03
Jane                 2100        Listing         2017-12-03

(6 row(s) affected)


--

Open in new window

0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Tomas Helgi JohannssonCommented:
Hi!

The simplest way to do this is like this. Pawan Kumar showed same/similar solution but forgot one key element in the first part of the select for his query to work.
The column names in UNION needs to be the same name and type. Also the column-names in the select needs to match the columns of the insert statement both in name and type as well (or at least same datatype and in same column orders as the target table).
INSERT INTO individualtransactions
SELECT Seller Name, ,SoldPrice,'Seller' Role, Date
FROM transactions
UNION ALL
SELECT Listing Name,SoldPrice,'Listing' Role, Date
FROM transactions

Open in new window


Regards,
    Tomas Helgi
0
Pawan KumarDatabase ExpertCommented:
Tomas Helgi Johannsson

THIS IS THE EXACT COPY OF MY FIRST SOLUTION with unnecessary naming of 2 columns. I really dont understand how these naming will help.

>>The column names in UNION needs to be the same name and type. Also the column-names in the select needs to match the columns of the insert statement both in name and type as well (or at least same datatype and in same column orders as the target table).

So why do you think it will not work. ? I suggest you to try it and provide me a sample for which it will not work, I WOULD LOVE TO LEARN.

Note - You have given UNNECESSARY  naming for Seller/Listing , role..which is not required at all.

I request not make incorrect comments pls.
0
Tomas Helgi JohannssonCommented:
Pawan Kumar

You forgot to rename both the Seller and Listing columns to Name, UNION won't work without this.

Regards,
     Tomas Helgi
0
Pawan KumarDatabase ExpertCommented:
Hi Tomas,

>>You forgot to rename both the Seller and Listing columns to Name, UNION won't work without this.
The above is absolutely INCORRECT... IT IS WORKING.

Just for you I have created a solution. You can checkout the working example on fiddle from here...
http://sqlfiddle.com/#!9/b09903e/1

Please show me an example where it is not working?

Thanks,
Pawan
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.