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

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