Tags
Complex SQL Challenges, Complex TSQL Challenge, Interview Qs.SQL SERVER Questions, Interview Questions on SQL, InterviewQuestions, InterviewQuestions for SQL, Learn complex SQL, Learn SQL, Learn T-SQL, PL/SQL Challenges, Puzzles, Queries for SQL Interview, SQL, SQL 2012, SQL 2014, SQL 2014 Interview Questions, SQL Challenge, SQL Challenges, SQL pl/sql puzzles, SQL Puzzles, SQL Server 2012 Analysis Services, SQL SERVER Interview questions, SQL SERVER Puzzles, SQL SERVER2005/2008, SQL Sudoku, SQLSERVER, T SQL Puzzles, T-SQL Challenge, Tough SQL Challenges, Tough SQL Puzzles, TSQL, TSQL Challenge, TSQL Challenges, TSQL Interview questions, TSQL Queries
T-SQL Query | [ The Sequencer Puzzle ]
The puzzle is simple. We have solve this puzzle using numbers with jumps, e.g. for 3 rows with AAA the numbers would be 11,12,13; BBB-21,22; CCC-31,32; DDD-41,42,43,44;. Please check out the sample input and expected output for details.
Sample Input
| Orders | OrderItem |
| AAA | 1 |
| AAA | 2 |
| AAA | 3 |
| BBB | 1 |
| BBB | 2 |
| CCC | 1 |
| CCC | 2 |
| DDD | 1 |
| DDD | 2 |
| DDD | 3 |
| DDD | 4 |
Expected output
| Orders | OrderItems |
| AAA | 11,12,13 |
| AAA | 11,12,13 |
| AAA | 11,12,13 |
| BBB | 21,22 |
| BBB | 21,22 |
| CCC | 31,32 |
| CCC | 31,32 |
| DDD | 41,42,43,44 |
| DDD | 41,42,43,44 |
| DDD | 41,42,43,44 |
| DDD | 41,42,43,44 |
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
Script
Use the below script to generate the source table and fill them up with the sample data.
---
CREATE TABLE Sequencer
(
Orders VARCHAR(3)
,OrderItem INT
)
GO
INSERT INTO Sequencer(Orders,OrderItem) VALUES
('AAA' ,1),
('AAA' ,2),
('AAA' ,3),
('BBB' ,1),
('BBB' ,2),
('CCC' ,1),
('CCC' ,2),
('DDD' ,1),
('DDD' ,2),
('DDD' ,3),
('DDD' ,4)
---
|
UPDATE – 20-Apr-2015 – Solution 1
--
;WITH CTE AS
(
SELECT r.Orders , CAST(rnk * 10 + OrderItem AS VARCHAR(3)) rnk FROM
(
SELECT * , ROW_NUMBER() OVER ( ORDER BY (SELECT NULL) ) rnk FROM
(
SELECT DISTINCT ORDERS FROM Sequencer
) d
) r
INNER JOIN Sequencer a on r.Orders = a.Orders
)
SELECT Orders , STUFF ((SELECT ',' + rnk FROM CTE w WHERE w.Orders = y.Orders FOR XML PATH('')) , 1,1,'') OrderItems
FROM CTE y
--
|
Add a comment if you have any other solution in mind. We all need to learn.
Keep Learning
Improving my SQL BI Skills 
with cte as
(select orders,(DENSE_RANK() over ( order by orders)*10+orderitem)[Ord1] from Sequencer)
select orders,
ord=stuff((select ‘,’+ cast(ord1 as varchar) from cte x where x.Orders=cte.Orders for xml path(”)),1,1,”)
from cte
LikeLike
;with cte
as
(
select distinct orders
from #Sequencer
)
,cte1
as
(
select *,ROW_NUMBER() over (order by orders) as rwnum
from cte
)
,cte2
as
(
select a.Orders,cast(a.rwnum as varchar(10))+cast(b.OrderItem as varchar(10)) as orderitems from cte1 a
join #Sequencer b
on a.Orders=b.Orders
)
select orders,STUFF((select ‘,’+ orderitems from cte2 a
where a.Orders=b.Orders for xml path(”)),1,1,”) as ordersItems
from cte2 b
LikeLiked by 1 person
SELECT Orders,
STUFF((SELECT ‘ ,’+Cast(OrderItem AS VARCHAR(20)) FROM Sequencer B WHERE B.Orders=A.Orders FOR XML PATH(”)),1,2,”) AS OrderItems
FROM Sequencer A
LikeLike