Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Dynamic Pivot for multiple columns Expand / Collapse
Author
Message
Posted Wednesday, July 17, 2013 6:57 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 1, 2014 2:28 AM
Points: 47, Visits: 110
Hi,
Here is my friend's sql server version info :

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 have google and implemented one column pivot i.e. NET QTY column by the help of below link :
http://www.sqlteam.com/article/dynamic-cross-tabs-pivot-tables

But here he needs to pivot more than one column with dynamic pivoting. I am sure, it has been asked many times, if you please share any link and/or solution, we shall be thankful to you.

Kindly let me know, if I am unclear in my question and/or needs to provide more info.

Thanks and Regards
Girish Sharma
Post #1474579
Posted Wednesday, July 17, 2013 8:58 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 10:05 AM
Points: 3,774, Visits: 8,465
There are 2 great articles from Jeff Moden. Here's the part on dynamic pivoting
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1474667
Posted Wednesday, July 17, 2013 9:00 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 1, 2014 2:28 AM
Points: 47, Visits: 110
I solved the question with the help of below link :
http://stackoverflow.com/questions/14694691/sql-server-pivot-table-with-multiple-column-aggregates

declare @Sql nvarchar(max)
select @Sql='SELECT min(t.code) As CODE,PRODNAME'
DECLARE @ColumnTemplate NVARCHAR(MAX)
select @ColumnTemplate='SUM(CASE WHEN t.department=''{department}'' THEN t.netqty ELSE 0 END) AS "{department}_NetQty"
,SUM(CASE WHEN t.department=''{department}'' THEN t.netmrp ELSE 0 END) AS "{department}_NetMrp"'
SELECT @Sql=@Sql+CHAR(13)+','+REPLACE(@ColumnTemplate, '{department}', REPLACE(c.name,'''',''''''))
FROM (
SELECT DISTINCT t.department AS name
FROM consumption t
) c
SELECT @Sql=@Sql+'FROM consumption t GROUP BY t.prodname order by code;'
--PRINT @Sql;
EXEC(@Sql);

Regards
Girish Sharma
Post #1474866
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse