Declare @sql varchar(8000)
Declare @itemid varchar(10)
Declare @itemName varchar(200)
Declare Cur Cursor For Select DICT_ITEM_ID, ITEM_NAME From BASE_DICT_ITEM where dict_id = 'promotionMode'
set @sql = 'select ty.main_id, '
Open Cur
Fetch next From Cur Into @itemid, @itemName
While @@fetch_status=0
Begin
--print @itemid
set @sql = @sql + 'MAX(CASE ty.[TYPE_ID] WHEN ' + @itemid + ' THEN '''+@itemName+','' ELSE '''' END) + '
Fetch Next From Cur Into @itemid, @itemName
End
set @sql = subString(@sql, 1, len(@sql)-2)
set @sql = @sql+ ' types from BIZ_PROM_TYPES ty, BASE_DICT_ITEM item where ty.MAIN_ID = item.DICT_ITEM_ID group by ty.main_id'
--print @sql
Close Cur
Deallocate Cur
exec (@sql)