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

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.

score 1 · Answer 1 · answered Jul 17 '13 at 13:55

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

Msg 208, Level 16, State 0, Line 15 Invalid object name '#Pivot'. — Girish Sharma, Jul 17 '13 at 14:34
yoou cant just copy and paste. This is an example that you can adapt for yourself. #Piviot is a temp table i created myself. Essentialy the part that transposes the data is the last part of the code. — Dev N00B, Jul 17 '13 at 14:41

score 0 · Answer 2 · answered Jul 17 '13 at 14:42

Firstly didn't saw SQL 2000, because i was busy in solving the Question.

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)
;

DECLARE @cols1 AS NVARCHAR(MAX),
    @query1  AS NVARCHAR(MAX),
    @query2  AS NVARCHAR(MAX);

SET @cols1 = STUFF((SELECT distinct ',' + QUOTENAME(c.department) 
            FROM consumption c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')



set @query1 = 'select t.code,t.prodname,
                t.AKD as netqtyAKD,t1.AKD as netmrpAKD,
                t.MRI as netqtyMRI,t1.MRI as netmrpMRI,
                t.PHYSIOTHERAPY as netqtyPHYSIOTHERAPY,t1.PHYSIOTHERAPY as netmrpPHYSIOTHERAPY,
                t.SONO as netqtySONO,t1.SONO as netmrpSONO
                from (
            SELECT code,prodname, ' + @cols1 + ' from 
            (
                select code
                    , prodname
                    , department
                    , sum(netqty) as netqty
                from consumption
                group by code
                    , department
                    , prodname
           ) x
            pivot 
            (
                 sum(netqty)
                for department in (' + @cols1 + ')
            ) p 
            )t
            inner join
            (SELECT code,prodname, ' + @cols1 + ' from 
            (
                select code
                    , prodname
                    , department
                    , sum(netmrp) as netmrp
                from consumption
                group by code
                    , department
                    , prodname
           ) x
            pivot 
            (
                 sum(netmrp)
                for department in (' + @cols1 + ')
            ) p
            )t1
            on t.code=t1.code
            and t.prodname=t1.prodname'

Dear downvoter please help me understand about how should one post his/her answer. — Prahalad Gaggar, Jul 17 '13 at 15:14
Thank you for your answer. In my table data, departments are dynamic, so I can't hard code the departments something like t.AKD as...t.MRI as... I am able to dynamic pivot one column with the help of http://www.sqlteam.com/article/dynamic-cross-tabs-pivot-tables link, but i need to add one more column and here I am stucking. Kindly show me your code in action with output please. — Girish Sharma, Jul 17 '13 at 15:35
I solve the question with the help of below link http://stackoverflow.com/questions/14694691/sql-server-pivot-table-with-multiple-column-aggregates. I don't know how do I post my code and output here, but question solved. — Girish Sharma, Jul 18 '13 at 02:57

Dynamic Pivot for multiple columns in SQL Server 2000

2 Answers2

Linked