June 14, 2013 at 3:37 am
It looks like you are missing concatenation
set @midcolumns = @midcolumns + 'colname' + quotename(@intflag + 1, '[') + ' nvarchar(max) null,';
As a separate note, I don't think you can create a column name like column[0]
June 14, 2013 at 3:56 am
This might be more efficient for creating the columns also you would need to put the ColNameXX into the square brackets like [ColName1] etc.
This will generate upto 256 columns in ant dataset.
DECLARE @Cols int = 20
DECLARE @ColList varchar(max)
;with L0
AS (SELECT n From (VALUES(1),(1)) b(n)) --2
,L1
AS (SELECT a.n From L0 a CROSS JOIN L0 b) --4
,L2
AS (SELECT a.n From L1 a CROSS JOIN L1 b) --16
,L3
AS (SELECT a.n From L2 a CROSS JOIN L2 b) --265
,CTE_InLineNumbersTable
AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) Number FROM L3)
select
@ColList= Convert(varchar(max)
,stuff(
(SELECT ', '+Quotename('colname'+convert(varchar,Number),'[')+' varchar(max)'+cHAR(10)
FROM
CTE_InLineNumbersTable
Where Number Between 2 and @Cols
for xml path (''))
,1,1,' '))
Print @ColList
the output here is
[colname2] varchar(max)
, [colname3] varchar(max)
, [colname4] varchar(max)
, [colname5] varchar(max)
, [colname6] varchar(max)
, [colname7] varchar(max)
, [colname8] varchar(max)
, [colname9] varchar(max)
, [colname10] varchar(max)
, [colname11] varchar(max)
, [colname12] varchar(max)
, [colname13] varchar(max)
, [colname14] varchar(max)
, [colname15] varchar(max)
, [colname16] varchar(max)
, [colname17] varchar(max)
, [colname18] varchar(max)
, [colname19] varchar(max)
, [colname20] varchar(max)
Be careful about using the Varchar(MAX) for each column definition.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
June 16, 2013 at 10:51 pm
thank you that worked a treat
Viewing 3 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply