SQL -- Nested Looping

I am having a table

Table A
--------
A
B
C
D

I need a result table as below

Table A
--------
A
B
C
D
AB
BC
CD
ABC
BCD

ABCD

I have tried the below code

DECLARE @tbl_wordlist TABLE (

[Id] int identity,

[Word] nvarchar(max)

)

INSERT INTO @tbl_wordlist([Word])values ('A'),('B'),('C'),('D')

-- LOGIC :::::: USING WHILE LOOP, GROUP TWO-TWO WORDS AND THEN INSERT INTO THE SAME TABLE

DECLARE @RunningCounter INTEGER

DECLARE @Counter INTEGER, @I INT

DECLARE @Txt VARCHAR(MAX)

SET @Counter = 1

SET @I = 2

SET @Txt = (SELECT [Word] FROM @tbl_wordlist WHERE ID = @Counter AND [Word] IS NOT NULL)

SET @RunningCounter = (SELECT MAX(ID) FROM @tbl_wordlist)

WHILE @Counter<@RunningCounter

BEGIN

IF @Txt!=''

SET @Txt=@Txt+' ' + (SELECT [Word] FROM @tbl_wordlist WHERE ID = (@I))

ELSE

SET @Txt=(SELECT [Word] FROM @tbl_wordlist WHERE ID=@Counter)

SET @Counter=@Counter+1

SET @I=@I+1

INSERT INTO @tbl_wordlist([Word])Values(@Txt)

SET @Txt = (SELECT [Word] FROM @tbl_wordlist WHERE ID = @Counter AND [Word] IS NOT NULL)

END

SELECT * FROM @tbl_wordlist;

Hi Nitisha,

Please try this code and let me know if it works for you

DECLARE @tbl_wordlist TABLE (
[Id] int identity,
[Word] nvarchar(max)
)
INSERT INTO @tbl_wordlist([Word])values ('A'),('B'),('C'),('D')
-- LOGIC :::::: USING WHILE LOOP, GROUP TWO-TWO WORDS AND THEN INSERT INTO THE SAME TABLE
DECLARE @RunningCounter INTEGER
DECLARE @Counter INTEGER, @I INT
DECLARE @Txt VARCHAR(MAX)
declare @s1 varchar(10)
declare @s2 varchar(10)
SET @Counter = 1
SET @I = 2
SET @Txt = (SELECT [Word] FROM @tbl_wordlist WHERE ID = @Counter AND [Word] IS NOT NULL)
SET @RunningCounter = (SELECT MAX(ID) FROM @tbl_wordlist)
WHILE @Counter<@RunningCounter
BEGIN
set @s1=(SELECT [Word] FROM @tbl_wordlist WHERE ID=@Counter)
set @s2=''
print @s1
while @I<=@RunningCounter
begin
set @s2=@s2+(SELECT [Word] FROM @tbl_wordlist WHERE ID=@I)
SET @Txt=@s1+@s2
INSERT INTO @tbl_wordlist([Word])Values(@Txt)
SET @i=@i+1
end
SET @Counter=@Counter+1
SET @i=@Counter+1
END
SELECT * FROM @tbl_wordlist
order by LEN([word]) ,id;

1 Reply

Know the answer? Post it — somebody with the same question will find it here.