CODEWITHSUNDEEP
sqlsql-serverloops
Japs
Japs

Reputation: 47

How do I use loop to generate column names dynamically?

I have table sdata and it has 35 columns (id, name, TRx1, TRx2, TRx3, TRx4,..., TRx30, city, score, total)

I want to fetch data from the TRx1,...TRx30 columns.

Can I use loop here?

I did following code:

DECLARE @flag INT
DECLARE @sel varchar(255)
DECLARE @frm varchar(255)

SET @flag = 1;
SET @sel = 'select TRx';
SET @frm = ' from sdata';

exec(@sel + 

    (WHILE @flag <=5
        @flag
    SET @flag = @flag + 1)

+ @frm)

What wrong am I doing? And how can I resolve this?

Upvotes: 0

Views: 1816

Answers (3)

pmbAustin
pmbAustin

Reputation: 3970

If your table name is sdata, this code should work for you:

-- Grab the names of all the remaining columns
DECLARE @sql nvarchar(MAX);
DECLARE @columns nvarchar(MAX);
SELECT @columns = STUFF ( ( SELECT N'], [' + name
            FROM sys.columns
           WHERE object_id = (select top 1 object_id FROM sys.objects where name = 'sdata') 
             AND name LIKE 'TRx%' -- To limit which columns
           ORDER BY column_id
             FOR XML PATH('')), 1, 2, '') + ']';
PRINT @columns
SELECT @sql = 'SELECT ' + @columns + ' FROM sdata';
PRINT @sql;
EXEC (@sql);

Note I included PRINT statements so you could see what's going on. You might want to comment out the EXEC while testing.

Upvotes: 3

Siyual
Siyual

Reputation: 16917

This would be much easier to do by just copy/pasting the column names and changing them to be the correct one. However if you must do it this way, I do not advise using a loop at all. This method uses a tally table to generate the columns you want to select (in this example, columns 1 through 30, but that can be changed), then generates a dynamic SQL statement to execute against the SData table:

Declare @From   Int = 1,
        @To     Int = 30,
        @Sql    NVarchar (Max)

Declare @Columns Table (Col Varchar (255))

;With Nums As
(
    Select  *
    From    (Values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) As V(N)
), Tally As
(
    Select  Row_Number() Over (Order By (Select Null)) As N
    From        Nums    A   --10
    Cross Join  Nums    B   --100
    Cross Join  Nums    C   --1000
)
Insert  @Columns
Select  'TRx' + Cast(N As Varchar)
From    Tally
Where   N Between @From And @To

;With Cols As
(
    Select  (
                Select  QuoteName(Col) + ',' As [text()]
                From    @Columns
                For Xml Path ('')
            ) As Cols
) 
Select  @Sql = 'Select ' + Left(Cols, Len(Cols) - 1) + ' From SData'
From    Cols

--Select    @Sql
Execute (@Sql)

Note: The --Select @Sql section is there to preview the generated query before executing it.

Upvotes: 1

Hogan
Hogan

Reputation: 70523

You can select the column names like this:

SELECT column_name 
FROM information_schema.columns
WHERE table_name = 'my name here'

Upvotes: 0

Related Questions