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 @ 11:55 AM
Points: 3,364, Visits: 7,279
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

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