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”.

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