Dynamic Columns
-
I have a table that has fields 'ColPosition'(int) and 'ColName'(char200) and a 'Jobid'(int). I will not know how many 'ColPosition' there are. From this I would like to create a table with as many fields as there are ColPositions for a particular Jobid, then parse the ColName and insert it into the ColPosition field. My hang up is in creating a table with an unknown amount of fields. Here is what I currently have for code. Any help would be appreciated. Thanks!
create proc Weblayout
@jobid int
as
declare @colnames as varchar(1000)
declare @firstloop as int
declare @secondloop as int
declare @splitstring as varchar(200)
declare @counter as int
drop table Webtable
create table Webtable
(
colname VARCHAR(1000)
)
set @counter = 1
set @firstloop = 'select max(ColPosition) from frtabledef where jobid ='& @jobid
while @firstloop > 0
begin
set @splitstring = 'select colname from frtabledef where jobid ='& @jobid & ' and colposition ='& @counter
--select dbo.ufn_parsestring('Recall 1 | Total Completes | Rotation A 431 1st/825 2nd'
insert into Webtable(colname) values (dbo.ufn_parsestring(@splitstring,'|',@counter))
set @counter = @counter + 1
end
select * from WebTable
-
Hi RKS,
quote:
I have a table that has fields 'ColPosition'(int) and 'ColName'(char200) and a 'Jobid'(int). I will not know how many 'ColPosition' there are. From this I would like to create a table with as many fields as there are ColPositions for a particular Jobid, then parse the ColName and insert it into the ColPosition field. My hang up is in creating a table with an unknown amount of fields. Here is what I currently have for code. Any help would be appreciated. Thanks!create proc Weblayout
@jobid int
as
declare @colnames as varchar(1000)
declare @firstloop as int
declare @secondloop as int
declare @splitstring as varchar(200)
declare @counter as int
drop table Webtable
create table Webtable
(
colname VARCHAR(1000)
)
set @counter = 1
set @firstloop = 'select max(ColPosition) from frtabledef where jobid ='& @jobid
while @firstloop > 0
begin
set @splitstring = 'select colname from frtabledef where jobid ='& @jobid & ' and colposition ='& @counter
--select dbo.ufn_parsestring('Recall 1 | Total Completes | Rotation A 431 1st/825 2nd'
insert into Webtable(colname) values (dbo.ufn_parsestring(@splitstring,'|',@counter))
set @counter = @counter + 1
end
select * from WebTable
maybe I am missing something, but webtable has only one field!
What about making your CREATE TABLE statement dynamic like this?
SET @stmt = N'CREATE TABLE ' + @tname +
..and then loop through your string?
EXEC sp_executeSQL @stmt
Is this going your direction?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Thanks Frank. I think you've got it but let me make sure. You're saying create the 'Create Table' statement dynamically by adding a field, (@tname) to the 'Create Table' statement during my loop then execute (@stmt) it at the end? I'm fairly new to SQL and still figuring out the syntax, what does the N in N'Create Table' do? And would I have to add the '(' after CREATE TABLE and ')' to the end of @stmt right before executing?
Thanks,
Richard.
-
Hi Richard,
quote:
Thanks Frank. I think you've got it but let me make sure. You're saying create the 'Create Table' statement dynamically by adding a field, (@tname) to the 'Create Table' statement during my loop then execute (@stmt) it at the end?well, the code I took it from looks like this
CREATE PROCEDURE blabl @tname nvarchar(50) AS
DECLARE @stmt nvarchar(whatever)
SET @stmt = N'CREATE TABLE ' + @tname +
' ( [id] [int] IDENTITY (1, 1) NOT NULL,[image] NULL ,
[filename] [varchar] (50) NULL ,
[description] [varchar] (100) NULL ,
[sender] [varchar] (50) NULL)
ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]'
EXEC sp_executeSQL @stmtin your case, you will have to loop through your string, plug in the return value from your function like
DECLARE @stmt nvarchar(4000)
DECLARE @retVal1 varchar(50), @retVal2 varchar(50)
DECLARE @a int
SET @retVal1='1'
SET @retVal2='1'
SET @stmt = N'CREATE TABLE MyTable ( '
SET @a=0
WHILE (@a<10)
BEGIN
SET @stmt = @stmt + '
[' +@retVal1 +'] [int] NULL,
[' +@retVal2 +'] [int] NULL)'
SET @a=@a+1
END
PRINT @stmttill the WHILE condition is met.
Then execute the string
quote:
I'm fairly new to SQL and still figuring out the syntax, what does the N in N'Create Table' do?actually it ensures the use of unicode characters. I guess not really needed, but I stick to it, because my first dynamic SQL statement I've built this way.
quote:
And would I have to add the '(' after CREATE TABLE and ')' to the end of @stmt right before executing?yes, it will not work without.
HTH
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
You are on the right track with Frank's reply. Just wanted to point out to watch out for the ampersands in your original procedure. The concatenation operator in SQL is the "+", not the "&". It's a common mistake if you're coming from Access to SQL Server. The "&" operator is the bitwise AND operator in SQL Server, something I'm pretty sure you didn't intend 🙂
-
Is this what you want
declare @max int
declare @ct int
declare @sql nvarchar(4000)
select @max = max(ColPosition)
from frtabledef
where jobid = @jobid
create table #newtable (Jobid int)
set @ct=0
while (@ct < @max)
begin
set @ct=@ct+1
set @sql = 'alter table #newtable add Col'+
cast(@ct as varchar)+
' varchar(20) null'
exec sp_executesql @sql
end
insert into #newtable (Jobid) values (@Jobid)
set @ct=0
while (@ct < @max)
begin
set @ct=@ct+1
set @sql = 'update a set a.Col'+
cast(@ct as varchar)+
' = dbo.ufn_parsestring(b.ColName,''|'','+
cast(@ct as varchar)+
')) from #newtable a inner join frtabledef b on b.Jobid = a.jobid and b.ColPosition = '+
cast(@ct as varchar)+
' where a.Jobid = '+
cast(@Jobid as varchar)
exec sp_executesql @sql
end
select * from #newtable
Sorry Frank, is that better.
Edited by - davidburrows on 07/28/2003 06:55:23 AM
Far away is close at hand in the images of elsewhere.
Anon. -
quote:
declare @max int
declare @ct int
declare @sql nvarchar(4000)
select @max = max(ColPosition) from frtabledef where jobid = @jobid
create table #newtable (Jobid int)
set @ct=0
while (@ct < @max)
begin
set @ct=@ct+1
set @sql = 'alter table #newtable add Col'+cast(@ct as varchar)+' varchar(20) null'
exec sp_executesql @sql
end
insert into #newtable (Jobid) values (@Jobid)
set @ct=0
while (@ct < @max)
begin
set @ct=@ct+1
set @sql = 'update a set a.Col'+cast(@ct as varchar)+' = dbo.ufn_parsestring(b.ColName,''|'','+cast(@ct as varchar)+')) from #newtable a inner join frtabledef b on b.Jobid = a.jobid and b.ColPosition = '+cast(@ct as varchar)+' where a.Jobid = '+cast(@Jobid as varchar)
exec sp_executesql @sql
end
select * from #newtable
what I really hate in this forum software is the missing automatic line wrap.
It's not very read-friendly !
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Sorry Frank, is that better.
Far away is close at hand in the images of elsewhere.
Anon. -
quote:
Sorry Frank, is that better.Hi David,
that's an inherent error in the forum software, it wasn't your post
But to answer I had to scroll vertical again
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Thanks Frank and Dave. Sorry for the delay, I got pulled into something else. I was able to get to where I needed to go from your input. Here's what I came up with.
CREATE procedure FTW
@jobid int
as
declare @max int
declare @maxouter int
declare @ct int
declare @gl int
declare @sql nvarchar(4000)
declare @pstring nvarchar(255)
--get max # of columns and create table with that many columns
select @max = max(ColPosition) from frtabledef where jobid = @jobid
create table #newtable (RowNum int identity (1,1) primary key, Jobid varchar(50))
set @ct=0
while (@ct < @max)
begin
set @ct=@ct+1
set @sql = 'alter table #newtable add Col'+
cast(@ct as varchar)+
' varchar(255) null'
exec sp_executesql @sql
end
--begin outer loop for amount of rows
set @maxouter = (select GroupLevels from frtableinfo where jobid = @jobid)
set @gl =0
while (@gl < @maxouter)
begin
set @gl=@gl+1
insert into #newtable (Jobid) values (@Jobid)
set @ct=0
--begin inner loop to populate columns
while (@ct < @max)
begin
set @ct=@ct+1
set @pstring = dbo.ufn_parsestring((select ColName from frtabledef where jobid = @jobid and ColPosition = @ct),'|',@gl)
--print @pstring
set @sql = 'update a set a.Col'+
cast(@ct as varchar)+
' = '''+@pstring+
''' from #newtable a inner join frtabledef b on b.Jobid = a.jobid and b.ColPosition = '+
cast(@ct as varchar)+
' where a.Jobid = '+
cast(@Jobid as varchar)+' and a.RowNum = '+cast(@gl as varchar)
exec sp_executesql @sql
--print @sql
end
end
select * from #newtable
GO
and here's the function I used to parse the string.
CREATE function ufn_parsestring (
@string nvarchar(255),
@delimiter nchar(1),
@value int)
returns nvarchar(255)
as
begin
declare @index int
declare @charpos int
declare @startpos int
set @index = 0
set @charpos = 1
set @startpos = 0
set @string = @string + @delimiter
while @index < @value - 1 and @charpos <= len(@string)
begin
set @charpos = @charpos + 1
if substring(@string,@charpos,1) = @delimiter
begin
set @index = @index + 1
end
end
if @charpos > len(@string)
begin
return NULL
end
if @value = 1
set @startpos = -1
else
set @startpos = @charpos + 1
set @charpos = @startpos
while substring(@string,@charpos,1) <> @delimiter and @charpos <= len(@string)
begin
set @charpos = @charpos + 1
end
if @charpos > len(@string)
begin
return NULL
end
return ltrim(substring(@string,@startpos,(@charpos) - @startpos))
endThanks again,
Richard.
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply