August 18, 2006 at 2:56 am
We have table (say table name “Store”) in which some columns are generated dynamically and data in dynamically generated columns filled by users. Now I want to make one search reports in that all the columns should display. I don’t have any problem for fixed columns but I am getting trouble in dynamically generated columns.
Say for example :
I have fixed columns in Store are ItemID,ItemName,ItemCost suppose now ItemColor and ItemHeight are generated dynamically then how I display that in report???
I can see the dynamically generated columns by
select syscolumns.name Name from syscolumns left join sysobjects
on sysobjects.ID = syscolumns.ID
where sysobjects.xtype = 'U' and sysobjects.name = ‘Store’ and syscolumns.name not in (‘ItemID’,’ItemName’,’ItemCost’)
OUTPUT: Name
ItemColor
ItemHeight
Now I made one stored procedure like
CREATE proc SP_AFSearch
as
declare @Column as varchar(100)
declare @strquery as varchar(1000)
Declare CRS_AddColumn CURSOR FOR select syscolumns.name Name from syscolumns left join sysobjects
on sysobjects.ID = syscolumns.ID
where sysobjects.xtype = 'U' and sysobjects.name = ‘Store’ and syscolumns.name not in (‘ItemID’,’ItemName’,’ItemCost’)
OPEN CRS_AddColumn
FETCH NEXT FROM CRS_AddColumn INTO @Column
WHILE @@FETCH_STATUS = 0
BEGIN
set @strquery = '(select Store.ItemID as [Item Code],Store.ItemName as [Item Name],Store.ItemCost as [Item Cost], and here I want that columns ItemColor and ItemHeight means @Column
from Store left join City
on Store.ItemID = City.ItemID)’
print @strquery
exec (@strquery)
FETCH NEXT FROM CRS_AddColumn INTO @Column
END
CLOSE CRS_AddColumn
DEALLOCATE CRS_AddColumn
GO
But I am unable to solve it please help me out
T.I.A
Regards,
Papillon
August 18, 2006 at 7:17 am
DECLARE @sql nvarchar(4000)
SET @sql = 'SELECT s.ItemID,s.ItemName,s.ItemCost'
SELECT @sql = @sql + ',s.' + c.[name]
FROM sysobjects o
INNER JOIN syscolumns c
ON c.id = o.id
AND c.[name] NOT IN ('ItemID','ItemName','ItemCost')
WHERE o.xtype = 'U' and o.name = 'Store'
ORDER BY c.colid
SET @sql = @sql + ' FROM Store s LEFT JOIN City c ON c.ItemID = s.ItemID'
EXEC sp_executesql @sql
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply