Sql - nested looping

Question

1.00/5 (1 vote)

See more:

SQL-Server-2012

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

Any help appreciated.

What I have tried:

I tried bringing two words combination.

DECLARE @tbl_wordlist TABLE (
    [Id]        int identity,
    [Word]   nvarchar(max)
)
INSERT INTO @tbl_wordlist
SELECT [Data] FROM Split('A B C D', ' ')

-- LOGIC :::::: USING WHILE LOOP, GROUP TWO-TWO WORDS AND THEN INSERT INTO THE SAME TABLE
DECLARE @MaxCount INTEGER
DECLARE @Count INTEGER, @I INT
DECLARE @Txt VARCHAR(MAX)
SET @Count = 1
SET @I = 2
SET @Txt = (SELECT [Word] FROM @tbl_wordlist WHERE ID = @Count AND [Word] IS NOT NULL)
SET @MaxCount = (SELECT MAX(ID) FROM @tbl_wordlist) 
WHILE @Count<@MaxCount
    BEGIN
    IF @Txt!=''
        SET @Txt=@Txt+' ' + (SELECT [Word] FROM @tbl_wordlist WHERE ID = (@I))
    ELSE
        SET @Txt=(SELECT [Word] FROM @tbl_wordlist WHERE ID=@Count)
    SET @Count=@Count+1
    SET @I=@I+1
    INSERT INTO @tbl_wordlist([Word])Values(@Txt)
    SET @Txt = (SELECT [Word] FROM @tbl_wordlist WHERE ID = @Count AND [Word] IS NOT NULL)
    END

SELECT * FROM @tbl_wordlist;

Posted 26-Oct-17 0:42am

Member 13486774

Updated 28-Oct-17 6:33am

Add a Solution

Comments

Karthik_Mahalingam 26-Oct-17 21:27pm

What about ac,bd,... Etc

Member 13486774 27-Oct-17 2:26am

No. I just want consecutive words to be together.

CHill60 27-Oct-17 14:16pm

You should not need any loops at all. When I get to a laptop I'll try to post a solution

2 solutions

Solution 2

I know you have already accepted an answer but as I said yesterday, there is no need to use a loop, in fact the number of times you need to use any loop in SQL is very rare - because it is SET based.

A very simple way of producing your output is just to use a series of self-joins...

SQL

-- Get the AB, BC, CD entries with a self join
INSERT INTO @tbl_wordlist 
SELECT A.Word + B.Word FROM @tbl_wordlist A
LEFT OUTER JOIN @tbl_wordlist B ON A.Id + 1 = B.Id
WHERE B.Word IS NOT NULL

-- Get the ABC, BCD entries with another self join
INSERT INTO @tbl_wordlist 
select A.Word + B.Word
from @tbl_wordlist A
LEFT OUTER JOIN @tbl_wordlist B ON A.Id + 5 = B.Id	-- NB The 5 here is important!
WHERE B.Word IS NOT NULL

-- Get the ABCD entry with yet another self join
INSERT INTO @tbl_wordlist 
select A.Word + B.Word
from @tbl_wordlist A
LEFT OUTER JOIN @tbl_wordlist B ON A.Id + 8 = B.Id	-- NB The 8 here is also important!
WHERE B.Word IS NOT NULL

There are other and better approaches that could be used, this is just the first one that came to mind.

If this is your homework then your tutor is probably going to start teaching you more about combinations soon, and they are absolutely not going to need you to keep coding loops in SQL (if they do then leave that college!). I got so passionate about loops being used I wrote an article about it - Processing Loops in SQL Server[^] - my article only touches the surface of what can be done (what should be done) in SQL without going anywhere near a conventional "loop" so do do more research.

Posted 28-Oct-17 6:33am

CHill60

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

itsmypassion · Accepted Answer · 2017-10-26T01:56:00

Modified, please try

SQL

DECLARE @tbl_wordlist TABLE (
    [Id]        int identity,
    [Word]   nvarchar(max)
)
INSERT INTO @tbl_wordlist
SELECT [Data] FROM Split--('A B C D', ' ')
 
DECLARE @COUNTER AS INTEGER

DECLARE @RUNNINGCOUNTER AS INTEGER 
DECLARE @NUMBEROFRUNS AS INTEGER = 1

--DECLARE @INNERCOUNTER AS INTEGER
DECLARE @TOTAL AS INTEGER  = (SELECT COUNT(*) FROM SPLIT)

SET @RUNNINGCOUNTER = (SELECT COUNT(*) FROM SPLIT)
sET @COUNTER = 1

DECLARE @WORD AS VARCHAR(MAX)

WHILE @RUNNINGCOUNTER > 0
BEGIN
	SET @NUMBEROFRUNS = 1
	WHILE @NUMBEROFRUNS <= @RUNNINGCOUNTER -1
	BEGIN

		SET @WORD = ''		
		SET @COUNTER = 1
		SET @WORD =''
		
		--PRINT 'RUN Counter '  + cast (@NUMBEROFRUNS as nvarchar(10))
		
		WHILE @COUNTER <= @TOTAL - (@RUNNINGCOUNTER -2)
		BEGIN
		--PRINT 'Counter '  + cast (@cOUNTER as nvarchar(10))
			SET @WORD = @WORD  + (SELECT WORD FROM @tbl_wordlist 
			WHERE ID =@NUMBEROFRUNS -1 + @COUNTER)
			SET @COUNTER=@COUNTER+1
		END
	
		pRINT @WORD
		INSERT INTO @tbl_wordlist (WORD) VALUES(@WORD)	
		
		
		SET @NUMBEROFRUNS =@NUMBEROFRUNS +1
	END
	
	SET @NUMBEROFRUNS = 1
	SET @RUNNINGCOUNTER = @RUNNINGCOUNTER -1
END
sELECT * FROM @tbl_wordlist