SQL Puzzle | The Seller Puzzle

In this puzzle we have seller and lister in the same row we have to separate them in two rows. Please check the sample input and expected output.

Sample Input

Expected Output

Script

Use below script to create table and insert sample data into it.

--

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

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

--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Solution – 1 | USING UNION ALL

--

SELECT Seller,SoldPrice,'Seller'Role,Date
FROM Sellers
UNION ALL
SELECT Listing,SoldPrice,'Listing',Date
FROM Sellers;

--

Output – 1

--

/*------------------------
SELECT Seller,SoldPrice,'Seller'Role,Date
FROM Sellers
UNION ALL
SELECT Listing,SoldPrice,'Listing',Date
FROM Sellers;
------------------------*/
Seller               SoldPrice   Role    Date
-------------------- ----------- ------- ----------
John                 1900        Seller  2017-12-01
Jane                 2400        Seller  2017-12-02
Joe                  2100        Seller  2017-12-03
Bill                 1900        Listing 2017-12-01
Becky                2400        Listing 2017-12-02
Jane                 2100        Listing 2017-12-03

(6 row(s) affected)


                                            
--

Solution – 2 | Using Cross Apply

--


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

--

Output – 2

--

/*------------------------
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
------------------------*/
Seller               SoldPrice   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)

                                       
--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com