User137014332 posted
Hi all,
i am making a report.rdlc which depend on the statment
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
-- exec dbo.N_Items_News_rep 1,0,'1/1/1900','1/1/1900',0,7703
ALTER PROCEDURE dbo.N_Items_News_rep
@Main_class int,
@Sub_class int ,
@date_from datetime =NULL ,
@date_to datetime =NULL,
@country int,
@NewID int
as
Set noCount on
Create table #Table1 (id int identity (1,1) ,Item_data varchar (50) default null ,Item_name varchar (50) default null,New_ID int,New_Date datetime)
insert into #Table1
SELECT Items_News.Item_Data, Items.item_name,Items_News.New_ID,News.New_Date
FROM Items_News INNER JOIN Items ON Items_News.Item_ID = Items.Item_ID INNER JOIN
News ON Items_News.New_ID = News.New_ID
where (Items.show_on_report = 1) and (Items.MainClass_ID=@Main_class or @Main_class=0) and (Items.SubClass_ID=@Sub_class or @Sub_class=0)
and((News.New_Date between @date_from and @date_to) or (@date_from='1/1/1900' and @date_to ='1/1/1900'))
--and (News.New_Date between @date_from and @date_to) or (@date_from = '1/1/1900' and @date_to = '1/1/1900')
and (News.Src_Cntry_ID=@country or @country=0) and (News.New_ID=@NewID)
--new add
Declare @FirstCol varchar (50)
Select top 1 @FirstCol=Item_name from #Table1 --where id=1--create queryto get first column
Declare @sql3 varchar (1000)
if(@FirstCol is not null)
begin
set @sql3='Create table table3 (id int identity (1,1),New_ID int,New_Date datetime,['+@FirstCol+'] varchar (50))'
exec(@sql3)
--till here
Declare @sql varchar (1000)
DEclare @colName varchar (50)
DECLARE @ItemName CURSOR
SET @ItemName = CURSOR FOR
SELECT distinct Item_name FROM #Table1
open @ItemName
FETCH NEXT
FROM @ItemName INTO @colName
WHILE @@FETCH_STATUS = 0
Begin
--Work Area
if(@colName!=@FirstCol)--change here
Begin
set @sql=' alter table table3 add ['+@colName+'] varchar (50) default null'
exec(@sql)
End
FETCH NEXT
FROM @ItemName INTO @colName
END
CLOSE @ItemName
DEALLOCATE @ItemName
insert into table3 (new_id)
Select distinct new_id from #table1
DEclare @colName1 varchar (50)
DECLARE @ItemName1 CURSOR
Declare @sql1 varchar (8000)
SET @ItemName1 = CURSOR FOR
SELECT distinct Item_name FROM #Table1
open @ItemName1
FETCH NEXT
FROM @ItemName1 INTO @colName1
WHILE @@FETCH_STATUS = 0
Begin
set @sql1='update table3 set ['+@colName1+']= #table1.item_data from table3,#table1 where table3.new_id=#table1.new_id and #table1.item_name like '''+@colName1+''' '
print (@sql1)
exec(@sql1)
FETCH NEXT
FROM @ItemName1 INTO @colName1
End
CLOSE @ItemName1
DEALLOCATE @ItemName1
Select distinct * from table3
drop table table3
end
drop table #Table1
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
which doesn't have a specific column name
how can i fill the table in the report
column value in table =Fields!columnname.Value (how can i get columnname, it is dynamic)
thanks
Generate Dynamic fields for report.rdlc
