January 26, 2011 at 6:02 am
hi all
am having a table like this .
TABLE NAME - GROUP
Name
------
xx
yy
aaa
zzzz
and i need to create a table like
Create Table #tname
(
xx varchar (50), --
yy varchar (50),
aaa varchar (50),
zzzz varchar (50)
)
i wanna create dynamically the column from GROUP TABLE ROWS..
PLEASE HELP ME>>>>
January 26, 2011 at 6:26 am
The below code is self explanatory and that should help, also look out to see for any other responses, there should be easier ways of doing this. I have choosen to create a global temp table, as we create a dynamic table using sp_executesql
create table #abcd(col1 varchar(50))
insert into #abcd values('xx')
insert into #abcd values('yy')
insert into #abcd values('aaa')
insert into #abcd values('zzzz')
declare @BuildTableStr nvarchar(1000),@currcolname varchar(50)
declare @maxcolcnt int,@i int
set @i=1
declare @tab1 table(id int identity(1,1), colname varchar(50))
set @BuildTableStr = 'create table ##tname('
insert into @tab1
select * from #abcd
select @maxcolcnt = MAX(id) from @tab1
while (@i<=@maxcolcnt)
begin
select @currcolname = colname from @tab1 where id = @i
set @BuildTableStr = @BuildTableStr + CASE WHEN @i< @maxcolcnt THEN ' ' + @currcolname + ' ' + 'varchar(50),'
WHEN @i=@maxcolcnt THEN ' ' + @currcolname + ' ' + 'varchar(50)'
END
set @i=@i+1
end
set @BuildTableStr = @BuildTableStr + ')'
PRINT @BuildTableStr
exec sp_executesql @BuildTableStr
Sriram
January 31, 2011 at 12:16 am
HI thanks for your reply.... its working good..
But i had some issues on thats..
here i have attached the sample xls sheet.
in that i need to insert these rows as columns in dynamically...
Please help me.....
in excel sheet having 13 rows.. i need to insert the 13 rows as colums..
in future it may be extent..
so need dynamically ...
January 31, 2011 at 12:21 am
HI thanks for your reply.... its working good..
But i had some issues on thats..
here i have attached the sample xls sheet.
in that i need to insert these rows as columns in dynamically...
Please help me.....
in excel sheet having 13 rows.. i need to insert the 13 rows as colums..
in future it may be extent..
so need dynamically ...
January 31, 2011 at 2:37 am
Here my sp code ,
its showing error like this :
--------------------------------
(95 row(s) affected)
create table ##tname( UnderwritingOld DECIMAL(9,2), LeaseAbstract DECIMAL(9,2), Accounting DECIMAL(9,2), HighYieldInvestments DECIMAL(9,2), AssetSummaryGroup DECIMAL(9,2), OriginationUnderwritingGroup DECIMAL(9,2), PortfolioUnderwritingGroup DECIMAL(9,2), InformationServicesAMG DECIMAL(9,2), FinancialAnalysisAMG DECIMAL(9,2), WachoviaServicingITAC DECIMAL(9,2), MortgageLoanServicing DECIMAL(9,2), WachoviaServicingOSAR DECIMAL(9,2), MortgageUnderwritingOld DECIMAL(9,2), LeaseAdministration DECIMAL(9,2), DocumentManagementGroup DECIMAL(9,2), WachoviaServicingLIATS DECIMAL(9,2), MasterServicing DECIMAL(9,2), MortgageLoanProcessing DECIMAL(9,2), sdgmaintainance DECIMAL(9,2), FacilityManagement DECIMAL(9,2), AdminManagement DECIMAL(9,2), AMGManagement DECIMAL(9,2), CallCenterManagement DECIMAL(9,2), CMGManagement DECIMAL(9,2), CSGManagement DECIMAL(9,2), OperationsManagement DECIMAL(9,2), FinanceManagement DECIMAL(9,2), ComplianceandHRRecords DECIMAL(9,2), EmployeeRelations DECIMAL(9,2), Pe
Msg 173, Level 15, State 1, Line 1
The definition for column 'Pe' must include a data type.
Msg 208, Level 16, State 1, Procedure LLLL, Line 56
Invalid object name '##tname'.
--------------------------------
--- code ---
ALTER PROCEDURE LLLL
AS
DECLARE @NoofDays INT,@R INT,@Q INT
DECLARE @MaxRows int
declare @ExecSql nvarchar(max)
create table #abcd(col1 varchar(50))
CREATE TABLE #tTables
(
numID INTEGER IDENTITY(1,1)
,strTableName SYSNAME
)
INSERT INTO #tTables (strTableName)
SELECT Replace(Replace(Replace(vchar_sub_biz_group_name, '-', ''), '&', ''), ' ', '') FROM CDB.DBO.CDB_SUB_BUSINESS_GROUP_LISTING
select @maxrows = count(numid) from #ttables
set @r = 1
while @r <= @maxrows
BEGIN
select @ExecSql = strTableName from #ttables where numid = @r
Insert into #abcd values(@ExecSql)
END
select * from #abcd
--select * from #ttables
--insert into #abcd values('xx')
--insert into #abcd values('yy')
--insert into #abcd values('aaa')
--insert into #abcd values('zzzz')
declare @BuildTableStr nvarchar(1000),@currcolname varchar(50)
declare @maxcolcnt int,@i int
set @i=1
declare @tab1 table(id int identity(1,1), colname varchar(50))
DROP TABLE ##TNAME
set @BuildTableStr = 'create table ##tname('
insert into @tab1
select * from #abcd
select @maxcolcnt = MAX(id) from @tab1
while (@i<=@maxcolcnt)
begin
select @currcolname = colname from @tab1 where id = @i
set @BuildTableStr = @BuildTableStr + CASE WHEN @i< @maxcolcnt THEN ' ' + @currcolname + ' ' + 'DECIMAL(9,2),'
WHEN @i=@maxcolcnt THEN ' ' + @currcolname + ' ' + 'DECIMAL(9,2)'
END
set @i=@i+1
end
set @BuildTableStr = @BuildTableStr + ')'
PRINT @BuildTableStr
exec sp_executesql @BuildTableStr
SELECT * from ##tname
GO
EXEC LLLL
Please tell me the solutions for this
January 31, 2011 at 8:36 am
Got the solutions..
Thanks
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply