Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Puzzle | T-SQL Query Problem

Puzzle Statement

I go this question on Experts Exchange. In this puzzles you first have to get distinct rows from column3 and move the extra values from ColumnA to ColumnB. Check out the input and expected output required.

Sample Input

ColumnA ColumnB Column3
123 NULL 555
456 NULL 666
789 NULL 777
ABC NULL 555
DEF NULL 666
GHI NULL 777

Expected output

ColumnA ColumnB Column3
ABC 123 555
DEF 456 666
GHI 789 777

Script

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

--


CREATE TABLE Column3
(
	 ColumnA VARCHAR(3)      
	,ColumnB VARCHAR(3)      
	,Column3 INT
)
GO

INSERT INTO Column3 VALUES
('123',NULL ,555),
('456',NULL ,666),
('789',NULL ,777),
('ABC',NULL ,555),
('DEF',NULL ,666),
('GHI',NULL ,777)
GO

--

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 #

--


;WITH CTE AS
(
	SELECT ColumnA , ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) rnks1
	FROM Column3
),
CTE1 AS
(
	SELECT Column3 , COUNT(*) OVER() Cnts , 
	ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) r1
	FROM Column3
	GROUP BY Column3
)
,CTE2 AS
(
	SELECT ColumnA , ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) r2
	FROM CTE WHERE rnks1 > (SELECT TOP 1 Cnts FROM CTE1 )
)
,CTE3 AS
(
	SELECT ColumnA ColumnB, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) r3
	FROM CTE WHERE rnks1 <= (SELECT TOP 1 Cnts FROM CTE1 )
)
SELECT CTE2.ColumnA , ColumnB , CTE1.Column3 FROM 
CTE1 INNER JOIN CTE2 on CTE1.r1 = CTE2.r2 INNER JOIN CTE3 ON CTE2.r2 = CTE3.r3


--

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 Kumar Khowal 

Http://MSBISkills.com

Advertisements