Tags

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


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

http://MSBISkills.com

Advertisements