Dynamic Pivot for multiple columns in SQL Server 2000 -
i know, bad etiquette post same question link in other forum, here question link :
http://www.sqlservercentral.com/forums/topic1474579-8-1.aspx
microsoft sql server 2000 - 8.00.2039 (intel x86) may 3 2005 23:18:38 copyright (c) 1988-2003 microsoft corporation enterprise edition on windows nt 5.2 (build 3790: service pack 2)
table structure , sample insert statements :
create table consumption ( code varchar(6), prodname varchar(50), department varchar(20), netqty decimal(10,2), netmrp decimal(10,2) ) insert consumption values ('u00180','usg jelly im-k','sono',11.4,130.40) insert consumption values ('u00280','unidine 1 litre solution','akd',1.4,10.40) insert consumption values ('v02401','volini gel 50gm','physiotherapy',8,15) insert consumption values ('v02402','x ray film digital 14"x 17"','mri',3,26.40) insert consumption values ('u00380','tropac p drops ','akd',21.46,56.78) insert consumption values ('u00380','tropac p drops ','akd',10,10) table data :
code prodname department netqty netmrp u00180 usg jelly im-k sono 11.40 130.40 u00280 unidine 1 litre solution akd 1.40 10.40 v02401 volini gel 50gm physiotherapy 8.00 15.00 v02402 x ray film digital 14"x 17" mri 3.00 26.40 u00380 tropac p drops akd 21.46 56.78 u00380 tropac p drops akd 10.00 10.00 required output please :
code prodname akd mri physiotherapy sono net qty net mrp net qty net mrp net qty net mrp net qty net mrp u00180 usg jelly im-k 11.40 130.40 u00280 unidine 1 litre solution 1.40 10.40 u00380 tropac p drops 31.46 66.78 v02401 volini gel 50gm 8.00 15.00 v02402 x ray film digital 14"x 17" 3.00 26.40 i sorry, post question in such way, if can please me looking above link, shall thankful help.
just because think in forum, there less activity, trying here.
update:question solved , posted solution in same above link. participated , helped me.
its pain pivot in sql2000 here sample code created pivot multiple columns using cursors
declare @sql nvarchar(4000) declare @taskname nvarchar(100) set nocount on create table #tbltline ( [dt] varchar(200) ) create table #tbltasks ( [tasks] varchar(200) ) insert #tbltasks ( [tasks] ) select distinct name #pivot insert #tbltline ( [dt] ) select distinct [dt] #pivot order dt --where active = 1 -- build table declare cur cursor select distinct [tasks] #tbltasks open cur fetch next cur @taskname while @@fetch_status = 0 begin set @sql = 'alter table #tbltline add [' + @taskname + '] nchar(1) null' exec (@sql) set @sql = '' set @sql = 'update #tbltline set [' + @taskname + '] = ''0''' exec (@sql) fetch next cur @taskname end close cur deallocate cur -- update table declare @sqlupdate nvarchar(4000) declare @time nvarchar(100) declare @name nvarchar(100) declare @active nchar(1) declare curupdate cursor select [dt], [name], [active] #pivot active = 1 open curupdate fetch next curupdate @time, @name, @active while @@fetch_status = 0 begin set @sqlupdate = 'update #tbltline set [' + @name + '] = ''1'' [dt] = ''' + @time + '''' exec (@sqlupdate) fetch next curupdate @time, @name, @active end close curupdate deallocate curupdate set nocount off 
Comments
Post a Comment