SQL Puzzle | T-SQL Query Problem | Improving my SQL BI Skills

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

3 thoughts on “SQL Puzzle | T-SQL Query Problem”

Marcus Hamilton said:

December 20, 2015 at 3:15 AM

Not sure if I fully understood the logic of the requirement, but here is a query that gives the desired result:

with cte
as
(
select row_number()
over(partition by column3 order by columna desc) as row_num,
columna,
column3
from column3
),
cte2
as
(
select row_num, columna, column3
from cte
where row_num = 1
)
select cte2.ColumnA as ColumnA,
cte.columna as ColumnB,
cte.Column3
from cte
inner join cte2
on cte.row_num cte2.row_num
and cte.column3 = cte2.column3;

LikeLike

Abhishek Dwivedi (@A_dwivedi) said:

January 21, 2016 at 11:28 AM

select column3,max(columna) as columna,columnb=min(columna) from column3 group by column3

LikeLike

Koren Borezky said:

April 2, 2016 at 3:58 PM

with cte as
(
select ind_int = try_convert(int, c.ColumnA) , * from Column3 c
)
select t1.ColumnA,ColumnB = c3.ColumnA ,t1.Column3
from cte as t1
join cte as c3
on c3.Column3=t1.Column3
where t1.ind_int is null and c3.ind_int is not null

LikeLike

M	T	W	T	F	S	S
			1	2	3	4
5	6	7	8	9	10	11
12	13	14	15	16	17	18
19	20	21	22	23	24	25
26	27	28	29	30	31

Share this

Related

3 thoughts on “SQL Puzzle | T-SQL Query Problem”

Leave a Reply to Koren Borezky Cancel reply