July 11, 2008 at 1:35 am
Dear all.,
I need your hellp on this..
i have a table screen_optional_field & screen_optional_data and i have field name called 'field_name' from screen_optional_field and its value are stored in screen_optional_data table in 'value' column.
Option_id Field_name Value
1 DOB 07/03/2008
2 Company Name Texas
3 Designation Jr.Software Engg
5 Account Number NULL
7 Incentive NULL
12 Address NULL
15 Salary 17500.50
16 Fax NULL
I wnt the o/p like this
DOB Companyname designation Account number Incentive
07/03/2008 Texas Jr.Software Engg NULL NULL
Address Salary Fax
NULL 17500.50 NULL
Can any one help me on this..
create table script
Create table Ref_screen
(
screen_id int identity(1,1),
screen_name nvarchar(50),
is_active bit default(1)
CONSTRAINT [pk_screen_id] PRIMARY KEY (screen_id)
)
Create table Screen_Optional_field
(
Option_id int identity(1,1),
Screen_id int,
Field_name nvarchar(50),
Data_type nvarchar(20),
Length int,
[Precision] int,
Scale int,
Nullable nvarchar(10),
Input_datedatetime default (getdate()),
Input_user nvarchar(4),
Update_date datetime,
Update_user nvarchar(4)
constraint [pk_option_id] primary key (option_id)
constraint [fk_screen_id] foreign key (screen_id)
references [ref_screen] (screen_id)
)
create table Screen_optional_data
(
Sod_id int identity(1,1),
Option_id int,
Primary_key_id int,
[Value] nvarchar(100),
Input_datedatetime default (getdate()),
Input_user nvarchar(4),
Update_date datetime,
Update_user nvarchar(4)
constraint [pk_sod_id] primary key (sod_id)
constraint [fk_option_id] foreign key (option_id)
references Screen_Optional_field (option_id)
)
Thanks..
July 11, 2008 at 7:01 am
Dear all.,
I have got the solution for my problem the following is the procedure i have created..
if object_id('get_screen_field_report')is not null
drop procedure get_screen_field_report
go
create procedure get_screen_field_report
(
@screen_id int
)
as
begin
if object_id('field_name')is not null
drop table field_name
if object_id('test1')is not null
drop table test1
if object_id('field_test')is not null
drop table field_test
set nocount on
DECLARE
@cols VARCHAR(2000),
@L_colname varchar(100),
@i int,@i1 int,
@L_cnt int
select
sof.option_id,
field_name,
isnull(value ,null)as value
into field_name
from screen_optional_field sof
left outer join screen_optional_data sod
on sof.option_id=sod.option_id
where screen_id=@screen_id
Select
@cols=COALESCE(rtrim(@cols)+',','')+''''+convert(char(60),'')+''''+
' as '+''''+rtrim(field_name)+''''
from field_name
--order by field_name
exec('select '+@cols+' into test1')
select
field_name,0 as printed
into field_test
from field_name
--order by field_name
set @i1=1
select @L_cnt= count(field_name) from field_test
while (@i1<=@L_cnt)
begin
set @i1=@i1+1
select top 1 @L_colname = field_name from field_test
where printed =0
--order by field_name
exec('declare @L_banlname varchar(100)
select top 1 @L_banlname = field_name from field_test
where printed =0
update test1
set ['+@L_colname+']=(select isnull(value,'''') from field_name where field_name =rtrim(@L_banlname))')
--order by field_name
update field_test
set printed=1
where field_name=@L_colnames
end
select @screen_id as Screen_id,* from test1
end
Exec--
get_screen_field_report 1
July 11, 2008 at 10:25 am
You could look at this post too, if you wanted some direction on how to avoid the dynamic sql...
http://www.sqlservercentral.com/Forums/Topic475805-338-1.aspx#bm476235
July 15, 2008 at 4:00 am
Here is the another solution fro my problem
Check it out...
alter procedure get_screen_report
(
@screen_id int
)
as
begin
drop table random
drop table temp
drop table temp1
Declare @field_name varchar(100),
@value varchar(100),
@num int,
@test-2 varchar(4000),
@test1 varchar(4000),
@val varchar(400),
@ins varchar(400)
select row_number() over (order by option_id) as
num,option_id,screen_id,ield_name into temp
from screen_optional_field
where screen_id=@screen_id
select row_number() over (order by sof.option_id)
as num,sof.option_id,
sof.screen_id,value into temp1
from screen_optional_field sof
left outer join
screen_optional_data sod on
sod.option_id=sof.option_id
where screen_id=@screen_id
set @num=1
while @num <= 1
Begin
select @field_name =field_name from temp where num=@num
select @value=value from temp1 where num=@num
select @test-2='create table random '+ + '([' + @field_name +']'+ ' varchar(400)' + ') '
exec(@test)
select @ins ='insert into random ' + + '([' + @field_name+ '])'+
+ ' values ' + +'('''+ isnull(@value,'') + ''')'
exec(@ins)
set @num=@num+1
end
while @num<= (select(max(num)) from temp)
Begin
select @field_name =field_name from temp where num=@num
select @value=value from temp1 where num=@num
select @test1 = ' Alter table random add ' +
+'['+@field_name + +']'+' varchar(200) '
exec(@test1)
select @val= 'update random set ' +'['++ @field_name + +']'+' = ''' + isnull(@value,'') +''''
exec(@val)
set @num=@num+1
End
select @screen_id Screen_id,* from random
End
get_screen_report 1
Thanks
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply