Quote:
Originally Posted by dotnetguru
Hi SMART GUYS,
Regards...
This was a nice challenge - i created a new_employee table to hold the results.
here it is based on your specific requirements:
CREATE TABLE [dbo].[new_employees] (
[userid] [int] NOT NULL ,
[name] [varchar] (50) NOT NULL ,
[role] [int] NOT NULL
) ON [PRIMARY]
GO
-- backup your employees table first
alter table employees add [ID] int identity(1,1) not null
go
declare @max int, @min int
set @max = (select max(id) from employees)
set @min = (select min(id) from employees)
declare @totcols int, @counter int
set @totcols = (select count(*) from employees where userid = 100)
set @counter = 1
declare @str varchar(50)
declare @sqlStr nvarchar(255)
while @counter <= @totcols
begin
set @str = 'total'+ convert(varchar(5),@counter)
set @sqlStr = N'alter table new_employees add '+ @str + ' int null'
exec sp_executesql @sqlStr
set @counter = @counter + 1
set @str = ''
set @sqlStr = ''
end
insert into new_employees (USERID, Name, Role)
select distinct USERID, Name, Role from employees
while @min <= @max
begin
set @counter = 1
while @counter <= @totcols
begin
set @str = 'total'+ convert(varchar(5),@counter)
set @sqlStr = N'update new_employees set ' + @str + ' = (select total from employees where id = ' + convert(varchar(5),@min) + ')' +
' where userid = (select userid from employees where id = ' + convert(varchar(5),@min) + ')'
exec sp_executesql @sqlStr
set @counter = @counter + 1
set @min = @min + 1
set @str = ''
set @sqlStr = ''
end
end
select * from new_employees
