SQL Server loop to table

Question

so I have received the following piece of code and am asked to rewrite it as a table:

USE [Database_name]

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[DumLoop]
AS

SET nocount ON
IF OBJECT_ID('dbo.DummyLoopy','U') IS NOT NULL DROP TABLE dbo.DummyLoopy
create table dbo.DummyLoopy
(value VARCHAR(1000))

DECLARE
@Counter INT = 1
,@MaxInteger INT =   ((select count(*) from  dbo.OUTPUT_DEF))
,@MaxInteger_2 INT = ((select count(*) from  dbo.OUTPUT_DEF))
,@MaxInteger_3 INT = ((select count(*) from  dbo.OUTPUT_DEF))
,@MaxInteger_4 INT = (select count(*) from  dbo.OUTPUT_TABLE)
,@MyNumber NVARCHAR(100)
,@JustAChar NVARCHAR (100)
,@SecondRow NVARCHAR(500)
,@1 NVARCHAR(100)
,@2 NVARCHAR(100)
,@3 NVARCHAR(100)

WHILE(@Counter <= @MaxInteger)
BEGIN

   SELECT 
   @MyNumber = convert(varchar(100))
   ,@JustAChar = '&'
   ,@SecondRow = '{8181:ABC12345' + convert(varchar(100), [ID]) + '}{123:45678}{LALA:'

   ,@1      = ':2020:' + '123456789' + convert(varchar(100), [ID])
   ,@2      = ':2323:ZUP'
   ,@3      = ':3333:1111' + convert(varchar(100), [ID]) + ',123'

FROM  dbo.OUTPUT_TABLE
WHERE main.[ID] = @Counter  

    insert into dbo.DummyLoopy Values('')
    insert into dbo.DummyLoopy Values(@JustAChar)
    insert into dbo.DummyLoopy Values(@SecondRow)
    insert into dbo.DummyLoopy Values(@1)
    insert into dbo.DummyLoopy Values(@2)
    insert into dbo.DummyLoopy Values(@3)

   SET @Counter  = @Counter
END

So this just writes every value on a newline and creates a table with only one column - the column value. I would like it also to write away every value as a new column. I don't even know how to go about it.

Output would then look something like:

&
:2020:1234567891
:2323:ZUP
:3333:11111,123

&
:2020:1234567892
:2323:ZUP
:3333:11112,123

&
:2020:1234567893
:2323:ZUP
:3333:11113,123

etc.

What do you mean "rewrite is as a table"? Do you mean you need to use a set based approach to get the same goal? Is this an interview question? — Thom A, Jul 07 '18 at 12:12
I added some sample output. I would like the output to be a regular table. So each first value (the "&" under one column, the :2020: output under the second column and so on) — Probs, Jul 07 '18 at 17:26

score 0 · Answer 1 · answered Jul 07 '18 at 11:26

0

You can use pivot to convert rows to columns in your final output instead of physically creating new columns for every value. Following link can help you out.

Efficiently convert rows to columns in sql server

If there is a must requirement to physically create new columns for every new value in while loop, then one way to achieve that is to use dynamic SQL commands.

Execute Dynamic SQL commands in SQL Server

answered Jul 07 '18 at 11:26

Osama Saleem

46
7

2

Rather than posting links, you'd be far better off including the relevant parts in your post. It's much more helpful to the OP, and also preserves the helpfulness of your post to others, should the link ever become dead. – Thom A Jul 07 '18 at 12:16

score 0 · Answer 2 · answered Jul 07 '18 at 23:51

you could do this :

DECLARE 
    @Counter     INT = 1
,   @ID          INT
,   @MaxInteger  INT 

IF OBJECT_ID('TempDB..#ID') IS NOT NULL
DROP TABLE #ID

SELECT
    ROW_NUMBER() OVER(ORDER BY ID) AS RN
,   ID 
INTO #ID 
FROM 
    dbo.OUTPUT_TABLE

IF OBJECT_ID('TempDB..#DummyLoopy') IS NOT NULL
DROP TABLE #DummyLoopy

CREATE TABLE #DummyLoopy([value] VARCHAR(1000)) 


SET @MaxInteger = (SELECT MAX(RN) FROM #ID) 

WHILE @Counter <= @MaxInteger
BEGIN 

    SET @ID = (SELECT ID FROM #ID WHERE RN = @Counter)

    INSERT INTO #DummyLoopy 
    VALUES  
        (' '),
        ('&'),
        ('{8181:ABC12345' + CONVERT(VARCHAR(100), @ID) + '}{123:45678}{LALA:'), 
        (':2020:' + '123456789' + CONVERT(VARCHAR(100), @ID)), 
        (':2323:ZUP'),
        (':3333:1111' + CONVERT(VARCHAR(100), @ID) + ',123')

    SET @Counter = @Counter + 1
END 

SELECT * FROM #DummyLoopy

I will try this out and provide feedback. But this would create a normal table right? — Probs, Jul 08 '18 at 09:13
@Probs no, it'll create a temporary table and insert the values inside it, you can then use the temporary table to insert all its values inside a permanent table (dbo.DummyLoopy) or whatever table you like. OR if you wish, you could insert it directly to the permanent table. do as you wish. — iSR5, Jul 08 '18 at 09:52

SQL Server loop to table

2 Answers2