SQL Puzzle: SQL Advance Query - Generate the group of Sequences

SQL Puzzle: SQL Advance Query – Generate the group of Sequences

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

Check the below input data and expected output to generate the group of sequences.

Input Data:

Name SeqNo

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

A 1

A 2

A 3

B 1

B 2

C 1

C 2

C 3

D 1

D 2

D 3

D 4

D 5

Expected Output:

Name SequenceNo_Groups

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

A 11,12,13

B 21,22

C 31,32,33

D 41,42,43,44,45

Create a table with sample data:

CREATE TABLE TestSequences

(

Name VARCHAR(3)

,SeqNo INT

)

INSERT INTO TestSequences

VALUES

('A' ,1)

,('A' ,2)

,('A' ,3)

,('B' ,1)

,('B' ,2)

,('C' ,1)

,('C' ,2)

,('C' ,3)

,('D' ,1)

,('D' ,2)

,('D' ,3)

,('D' ,4)

,('D' ,5)

Solution:

;WITH CTE AS

(

SELECT b.Name , CAST(rnk * 10 + SeqNo AS VARCHAR(3)) rnk FROM

(

SELECT * , ROW_NUMBER() OVER ( ORDER BY (SELECT NULL) ) rnk FROM

(

SELECT DISTINCT Name FROM TestSequences

) a

) b

INNER JOIN TestSequences a on b.Name = a.Name

)

SELECT Name, STUFF ((SELECT ',' + rnk FROM CTE w WHERE w.Name = t.Name FOR XML PATH('')) , 1,1,'') SequenceNo_Groups

FROM CTE t

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

Jul 29, 2019Anvesh Patel

Comments: 11

Dinesh

August 1, 2019 at 7:27 am

—Method-1
;
WITH CTE
As
(Select Distinct Name From TestSequences),
N
As
(Select ROw_Number()Over(Order By Name) As SLno,Name
From CTE),
R
As
(Select T.Name,(Select Convert(VarChar(500),SLNo)+Convert(Varchar(50),SeqNo)+’,’ From TestSequences TT Inner JOIn N On N.Name=TT.Name
Where T.Name=TT.Name For XML PATH(”)) As Result
From TestSequences T
)
Select Name,Left(Result,Len(Result)-1) as SequenceGroup From R

Reply Cancel

Sai Nath

August 11, 2019 at 6:40 am

; WITH CTE AS
(
SELECT
*,
DENSE_RANK() OVER(ORDER BY NAME) AS DN
FROM
TestSequences
)

SELECT
*,
STUFF((
SELECT
‘,’+CAST(B.DN AS VARCHAR(256))+CAST(B.SEQNO AS VARCHAR(256))
FROM
CTE AS B
WHERE
B.NAME = A.NAME
FOR XML PATH(”)),1,1,”)
FROM
CTE AS A

Reply Cancel

Brahma Teja K
February 27, 2020 at 2:31 pm
SELECT
TS.NAME,
Z.GROUPS
FROM
TestSequences TS
INNER JOIN
(
SELECT
S.NAME,
STRING_AGG(CAST(Y.RN AS VARCHAR(5))+CAST(S.SeqNo AS VARCHAR(5)),’,’) AS GROUPS
FROM
TestSequences S
INNER JOIN
(
SELECT
X.NAME,
ROW_NUMBER() OVER(ORDER BY X.NAME) AS RN
FROM
(
SELECT DISTINCT
NAME AS NAME
FROM
TestSequences
)X
)Y ON Y.NAME = S.Name

GROUP BY
S.NAME
)Z ON Z.NAME = TS.NAME
Reply Cancel

Pavan shetty

October 22, 2019 at 10:43 am

;WITH CTE
AS
(
SELECT CAST(DENSE_RANK()OVER(ORDER BY NAME)AS VARCHAR(2))+CAST(SEQNO AS VARCHAR(2)) SeqNonew,*
FROM TestSequences
)
SELECT Name,STUFF((SELECT ‘,’+SeqNonew FROM CTE I WHERE I.NAME=O.NAME FOR XML PATH(”) ),1,1,”) SequenceNo_Groups FROM CTE O

Reply Cancel

Samrat

October 24, 2019 at 6:40 am

WITH CTE
AS
(
SELECT O.SEQNO+(DENSE_RANK()OVER(ORDER BY O.NAME)*10) AS VAL,NAME
FROM TestSequences O)
SELECT NAME,STUFF((SELECT ‘,’+CAST(VAL AS VARCHAR) FROM CTE I WHERE I.NAME = O.NAME FOR XML PATH(”)),1,1,”) AS SEQ
FROM CTE O

Reply Cancel

koustav

October 25, 2019 at 11:31 am

select T1.Name,T1.total as Seq_no_group from
(select T.name,
array_to_string(array_agg(T.tot), ‘,’) total
from
(SELECT
Name,seqno,

Dense_RANK () OVER (
ORDER BY Name
) ||”||seqno as tot
FROM
TestSequences)T
group by T.name)T1,
TestSequences t2
where T1.Name=T2.Name

Reply Cancel

Keith Fernandes

October 27, 2019 at 1:11 pm

;with cte as
(
select I.Name,I.SeqNo,
dense_Rank() over (order by I.name) * 10 as rnk
from TestSequences I
)
select Name,(select STRING_AGG(rnk + SeqNo,’,’) from cte O where O.Name = I.Name group by Name) as SEQ from cte I

Reply Cancel

Brahma Teja K

February 27, 2020 at 2:27 pm

SELECT
TS.NAME,
Z.GROUPS
FROM
TestSequences TS
INNER JOIN
(
SELECT
S.NAME,
STRING_AGG(CAST(Y.RN AS VARCHAR(5))+CAST(S.SeqNo AS VARCHAR(5)),’,’) AS GROUPS
FROM
TestSequences S
INNER JOIN
(
SELECT
X.NAME,
ROW_NUMBER() OVER(ORDER BY X.NAME) AS RN
FROM
(
SELECT DISTINCT
NAME AS NAME
FROM
TestSequences
)X
)Y ON Y.NAME = S.Name