• Status: Solved
  • Priority: High
  • Security: Public
  • Views: 59
  • Last Modified:

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
0
Daniel Williams
Asked:
Daniel Williams
  • 5
  • 2
1 Solution
 
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
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now