SQL Puzzle: SQL Advance Query - Solve the challenge of DISTINCT

SQL Puzzle: SQL Advance Query – Solve the challenge of DISTINCT

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

Check the below input data and expected output to find a distinct value for column 3 and accordingly move additional data of Column 1 to Column 2.

Input Data:

Col1 Col2 Col3

----------- ----------- -----------

888 NULL 111

555 NULL 333

777 NULL 444

222 NULL 333

666 NULL 444

999 NULL 111

Expected Output:

Col1 Col2 Col3

----------- ----------- -----------

222 888 111

666 555 333

999 777 444

Create a table with data:

CREATE TABLE tbl_DataColumn

(

Col1 INT

,Col2 INT

,Col3 INT

)

INSERT INTO tbl_DataColumn VALUES

(888,NULL ,111)

,(555,NULL ,333)

,(777,NULL ,444)

,(222,NULL ,333)

,(666,NULL ,444)

,(999,NULL ,111)

Solution:

;WITH CTE AS

(

SELECT Col1 , ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) rnk

FROM tbl_DataColumn

CTE1 AS

(

SELECT

Col3

,COUNT(*) OVER() as Counts

,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) t1

FROM tbl_DataColumn

GROUP BY Col3

)

,CTE2 AS

(

SELECT

Col1

,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) t2

FROM CTE

WHERE rnk > (SELECT TOP 1 Counts FROM CTE1 )

)

,CTE3 AS

(

SELECT

Col1 Col2

,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) t3

FROM CTE

WHERE rnk <= (SELECT TOP 1 Counts FROM CTE1 )

)

SELECT

CTE2.Col1

,Col2

,CTE1.Col3

FROM CTE1

INNER JOIN CTE2

ON CTE1.t1 = CTE2.t2

INNER JOIN CTE3

ON CTE2.t2 = CTE3.t3

Please try the different solution for this puzzle and share it via comment...

Oct 6, 2017Anvesh Patel

Comments: 5

Dinesh I.S

October 9, 2017 at 1:17 pm

;
WITH C1
As
(Select Row_Number()Over(Order By Col3) As SLNo,Col3
From (Select Distinct Col3
From TBL_DataColumn) A) ,
C2
As
(Select Row_Number()Over(PARTITION By GSLNo Order By Col1) As SLNo,GSLNo,Col1
From (Select NTILE(2)Over(Order By Col1) As GSLNo,Col1 From TBL_DataColumn) A )
Select C2.Col1 As Col2,C3.Col1 As Col3,C1.Col3
From C1 Inner Join C2 On C1.SLNo=C2.SLNo
Inner JOin C2 As C3 On C3.SLNo=C1.SLNo
Where C2.GSLNo=1
And C3.GSLNo=2

Reply Cancel

Anvesh Patel
October 9, 2017 at 2:29 pm
Perfect!
Reply Cancel

Dinesh I.S

October 9, 2017 at 1:29 pm

—While Looping We can get Result

Declare @TableResult Table(Col1 Int,Col2 Int,Col3 Int)
Delete From @TableResult
Declare @Amt As Int
Select @Amt=Min(Col3) From TBL_DataColumn

While @Amt Is Not Null
Begin
Insert Into @TableResult
(Col1,Col2,Col3)
Select *,@Amt From (
Select Row_Number()Over(Order By Col1) As SLNo,Col1
From TBL_DataColumn Where Col3=@Amt)A
PIVOT(Max(Col1) For SLNo In([1],[2])) A
Select @Amt=Min(Col3) From TBL_DataColumn Where Col3>@Amt
End
Select * From @TableResult

Reply Cancel

Anvesh Patel
October 9, 2017 at 2:29 pm
Good!
Reply Cancel

Marcel Hofman

March 21, 2019 at 12:47 pm

; WITH firstcycle as
(
SELECT
ROW_NUMBER () OVER ( ORDER BY (SELECT 0) ) AS n
, col3
, col1 AS newcolumntwo
, NULL AS newcolumnone
FROM tbl_DataColumn
ORDER BY (SELECT 0)
OFFSET 0 ROWS
FETCH NEXT (SELECT COUNT(DISTINCT col3) FROM tbl_DataColumn) ROWS ONLY
)
,
secondcycle AS
(
SELECT
ROW_NUMBER () OVER ( ORDER BY (SELECT 0) ) AS n
, col3
, NULL AS newcolumntwo
, col1 AS newcolumnone
FROM tbl_DataColumn
ORDER BY (SELECT 0)
OFFSET (SELECT COUNT(DISTINCT col3) FROM tbl_DataColumn) ROWS
FETCH NEXT (SELECT COUNT(DISTINCT col3) from tbl_DataColumn) ROWS ONLY
)
SELECT s.newcolumnone AS col1, f.newcolumntwo AS col2, f.col3
FROM firstcycle f
LEFT JOIN secondcycle s
ON s.n -(SELECT COUNT(DISTINCT col3) FROM tbl_DataColumn) = f.n

Reply Cancel

SQL Puzzle: SQL Advance Query – Solve the challenge of DISTINCT

Leave a Reply to Dinesh I.S Cancel reply

Anvesh Patel

About Me!

About DBRND !

Recent Comments !

Follow Me !