dynamically creating a table

[DanC]

I'm working on trying to somewhat denormalize a table so that it's easier for some of the people to work with. I've got a table set up like this:

CREATE TABLE [def_tab] (
[defNo] [int] NOT NULL ,
[objNo] [int] NULL ,
[typeNo] [int] NULL ,
[defName] varchar(50) null )

where defNo is a sequential number and objNo and typeNo are both descriptive codes and defName is the definition name that I want to use as a column name. I've created a cursor to create the dynamic sql, but I can't get rid of the last comma:

DECLARE @SQLState VARCHAR(8000)
DECLARE @defName VARCHAR(100)

SET @SQLState = 'create table udf ('

DECLARE mycursor cursor for select DefName from def_tab where objNo = 1600 order by defNo
OPEN mycursor

FETCH NEXT FROM mycursor INTO @defName
WHILE @@FETCH_STATUS = 0

BEGIN
set @sqlstate = @SQLState + '[' + @defName + '] varchar(50) null, '
FETCH NEXT FROM mycursor INTO @defName
END

CLOSE mycursor
DEALLOCATE mycursor

SET @SQLState = @SQLState + ' )'

Print (@SQLState)

which gives me:
create table udf ([Unit Code] varchar(50) null, [Unit Desc] varchar(50) null, [Report Band] varchar(50) null, [Ethnicity] varchar(50) null, [Subgroup] varchar(50) null, [Department] varchar(50) null, [Group] varchar(50) null, [Jobtitle] varchar(50) null, )

As you can see, the last comma is unnecessary. Any one have a suggestion for how to work around this?

thanks

-D

 

[markVII]

Two choices:
1. Use LEFT function to strip last char (comma)
SET @SQLState = LEFT(@SQLState,LEN(@SQLState)-1) + ')'

2.Declare variable @first_time = 0; in your loop
if @first_time = 0
set @sqlstate = '[' + @defName + '] varchar(50) null'
set @first_time = 1
else
set @sqlstate = @SQLState + ',[' + @defName + '] varchar(50) null '


note where the comma is placed

 

[DanC]

thanks for the quick response. It's just what I was looking for.