Dynamic Pivot for multiple columns in SQL Server 2000

Question

I know, it is bad etiquette to post the same question link in other forum, but here it is my 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 and sample insert statements :

create table consumption (
code varchar(6),
prodname varchar(50),
department varchar(20),
netqty decimal(10,2),
netmrp decimal(10,2)
)

insert into consumption values ('U00180','USG JELLY IM-K','SONO',11.4,130.40)
insert into consumption values ('U00280','UNIDINE 1 LITRE SOLUTION','AKD',1.4,10.40)
insert into consumption values ('V02401','VOLINI GEL 50GM','PHYSIOTHERAPY',8,15)
insert into consumption values ('V02402','X RAY FILM DIGITAL 14"X 17"','MRI',3,26.40)
insert into consumption values ('U00380','TROPAC P DROPS ','AKD',21.46,56.78)
insert into 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 am sorry, to post the question in such a way, but if anyone can please help me by looking the above link, I shall be thankful for your help.

Just because I think in that forum, there is less activity, so I am trying here.

Update:Question is solved and I posted the solution in the same above link. Thanks to all who participated and helped me.

Answer 1

its a pain to pivot in SQL2000 but here is some sample code that I created to 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 INTO #tblTasks (
    [Tasks]
)
select DISTINCT
    Name
from #Pivot 


INSERT INTO #tblTLine (
    [DT]
)
select DISTINCT
    [DT]
from #Pivot 
ORDER BY DT
--WHERE Active = 1

-- Build Table
DECLARE cur CURSOR FOR

select DISTINCT
    [Tasks]
from #tblTasks

OPEN cur

FETCH NEXT FROM cur INTO @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 FROM cur INTO @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 FOR

SELECT 
    [DT],
    [Name],
    [Active]
FROM #Pivot 
WHERE Active = 1

OPEN curUpdate

FETCH NEXT FROM curUpdate INTO @Time, @Name, @Active

WHILE @@FETCH_STATUS = 0
BEGIN

    SET @SQLUpdate = 'UPDATE #tblTLine SET [' + @Name + '] = ''1'' WHERE [DT] = ''' + @Time + ''''
    EXEC (@SQLUpdate)

    FETCH NEXT FROM curUpdate INTO  @Time, @Name, @Active

END

CLOSE curUpdate
DEALLOCATE curUpdate


SET NOCOUNT OFF